Querying Records from a Xata Database

Querying the records of a table is done with the /query endpoint. Note that a POST request is used, even though the data is not modified, in order to be able to use a body for the request (GET requests with a body are non-standard).

The general form of a query request is:

// POST https://{your-workspace-slug}.xata.sh/db/{db_branch_name}/tables/{table_name}/query

{
  "columns": [...],
  "filter": {
    ...
  },
  "sort": {
    ...
  },
  "page": {
  }
}

All the body parameters are optional, so the simplest query request is just a POST request to https://tutorial-ng7s8c.xata.sh/db/tutorial:main/tables/teams/query

The response looks like this:

{
  "records": [
    {
      "id": "rec_c8hng2h26un90p8sr7k0",
      "name": "Matrix",
      "owner": {
        "id": "rec_c8hnbch26un1nl0rthkg"
      },
      "xata": {
        "version": 0
      }
    }
  ],
  "meta": {
    "page": {
      "cursor": "jMq7DcIwEIDhnjH-2sWRAsItAT2KkOU8bAgB3Zkqyu6IDei_",
      "more": false
    }
  }
}

Note that the id and xata.version are included in the returned records. We will discuss the meta.page object when we talk about paginating through the records.

Getting a Record by ID

You can retrieve a recorded with a given ID using the following request:

GET https://{your-workspace-slug}.xata.sh/db/{db_branch_name}/tables/{table_name}/data/{record_id}

For example, the following query:

GET https://tutorial-ng7s8c.xata.sh/db/tutorial:main/tables/users/data/rec_c8hod8h26un90p8sr7kg

Returns an object like:

{
  "address": {
    "street": "123 Main St",
    "zipcode": 12345
  },
  "email": "carrie@example.com",
  "full_name": "Carrie-Anne Moss",
  "id": "rec_c8hod8h26un90p8sr7kg",
  "team": {
    "id": "rec_c8hng2h26un90p8sr7k0"
  },
  "xata": {
    "version": 0
  }
}

Selecting Columns from the Linked Tables

You might have noticed in the above response that the owner column is a reference to a user, but only the id of that user is returned. You can easily return all the user columns for the referenced user, in a single query, by explicitly requesting them via the "columns" array in the request body:

// POST https://tutorial-ng7s8c.xata.sh/db/tutorial:main/tables/teams/query

{
  "columns": ["*", "owner.*"]
}

Now, the response includes all the users columns in the owner object:

{
  "records": [
    {
      "id": "rec_c8hng2h26un90p8sr7k0",
      "name": "Matrix",
      "owner": {
        "address": {
          "street": "123 Main St",
          "zipcode": 12345
        },
        "email": "keanu@example.com",
        "full_name": "Keanu Reeves",
        "id": "rec_c8hnbch26un1nl0rthkg",
        "team": {
          "id": "rec_c8hng2h26un90p8sr7k0"
        },
        "xata": {
          "version": 2
        }
      },
      "xata": {
        "version": 0
      }
    }
  ],
  "meta": {
    "page": {
      "cursor": "jMxBDoIwEAXQvcf4S9KY2oXiXEL3hJimlHYUi5mpcUG4u-",
      "more": false
    }
  }
}

The "columns": ["*", "owner.*"] syntax means: select all columns from this table, and also all the columns from the table referenced by the owner link column.

Being Selective About the Columns

You can specify the "columns" array in the request body to select only a subset of the columns you want to retrieve. For example, if you are only intersted in the team name and the email of the owner, you can make a request like this:

// POST https://tutorial-ng7s8c.xata.sh/db/tutorial:main/tables/teams/query

{
  "columns": ["name", "owner.email"]
}

We consider being selective about columns a best practice, as it can speed up your products built on Xata.

Filtering Records

To filter the results, use the filter field in the request body. For example:

// POST https://tutorial-ng7s8c.xata.sh/db/tutorial:main/tables/users/query

{
  "filter": {
    "email": "keanu@example.com"
  }
}

Will return only the records with the given email address:

{
  "records": [
    {
      "address": {
        "street": "123 Main St",
        "zipcode": 12345
      },
      "email": "keanu@example.com",
      "full_name": "Keanu Reeves",
      "id": "rec_c8hnbch26un1nl0rthkg",
      "team": {
        "id": "rec_c8hng2h26un90p8sr7k0"
      },
      "xata": {
        "version": 2
      }
    }
  ],
  "meta": {
    "page": {
      "cursor": "jMtBqsIwEAbg_TvGvw6PtAuRrLyB7qXImKbN2EkqkxSEkrtLb-D-",
      "more": false
    }
  }
}

To refer to nested columns, you can use either the dot notation:

// POST https://tutorial-ng7s8c.xata.sh/db/tutorial:main/tables/users/query

{
  "filter": {
    "address.zipcode": 12345
  }
}

Or the equivalent nested object:

// POST https://tutorial-ng7s8c.xata.sh/db/tutorial:main/tables/users/query

{
  "filter": {
    "address": {
      "zipcode": 12345
    }
  }
}

You can also refer to a linked column in filters, for example:

// POST https://tutorial-ng7s8c.xata.sh/db/tutorial:main/tables/users/query

{
  "filter": {
    "team.name": "Matrix"
  }
}

Note that in the above, name is a column in the teams table, and we can refer to it even when querying the users table.

To give a more complex filtering example, consider the following:

// POST https://tutorial-ng7s8c.xata.sh/db/tutorial:main/tables/users/query

{
  "filter": {
    "address.zipcode": {
      "$gt": 100
    },
    "$any": [
      {
        "full_name": { "$contains": "Keanu" }
      },
      {
        "full_name": { "$contains": "Carrie" }
      }
    ]
  }
}

Translating the above filter in English: filter all users with a zipcode greater than 100, and the full name contains either "Keanu" or "Carrie".

The example above demonstrates several operators:

  • $gt: which can be applied to number columns, and means "greater than".
  • $contains: which can be applied to string columns, and does a substring match.
  • $any: which can be used to create OR conditions. The record matches if any of the conditions enclosed are true.

To see the rest of the operators available, check out the API reference.

Paginating Results

When running a query, you can specify a particular page size. For example:

// POST https://tutorial-ng7s8c.xata.sh/db/tutorial:main/tables/users/query

{
  "page": {
    "size": 2
  }
}

Returns only the first two records:

{
  "records": [
    {
      "address": {
        "street": "123 Main St",
        "zipcode": 12345
      },
      "email": "keanu@example.com",
      "full_name": "Keanu Reeves",
      "id": "rec_c8hnbch26un1nl0rthkg",
      "team": {
        "id": "rec_c8hng2h26un90p8sr7k0"
      },
      "xata": {
        "version": 2
      }
    },
    {
      "address": null,
      "email": "laurence@example.com",
      "full_name": "Laurence Fishburne",
      "id": "rec_c8hnnh126unff00ifhhg",
      "team": {
        "id": "rec_c8hng2h26un90p8sr7k0"
      },
      "xata": {
        "version": 0
      }
    }
  ],
  "meta": {
    "page": {
      "cursor": "VMoxDsIwDAXQnWP8OUPSASFfAnZUoZDWtSEEyTFT1bujion9PdKK_",
      "more": true
    }
  }
}

In this case, notice that the meta.page objects contains "more": true. This is an indication that there are more records available. The "cursor" key is a pointer to the current page. To retrieve the next page of results, you can make a request like this:

// POST https://tutorial-ng7s8c.xata.sh/db/tutorial:main/tables/users/query

{
  "page": {
    "size": 2,
    "after": "VMoxDsIwDAXQnWP8OUPSASFfAnZUoZDWtSEEyTFT1bujion9PdKK_"
  }
}

You can continue like this until "more" is returned as false.

Next Steps

Now that we can get retrieve data from a database, we might be interested in creating more data, updating data or deleting data. We've got guides for each of these operations.


Last modified 1 mo1 day ago