Query Table Data

The Query Table API can be used to retrieve all records in a table. The API support filtering, sorting, selecting a subset of columns, and pagination.

The overall structure of the request looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// POST /db/<dbname>:<branch>/tables/<table>/query
{
  "columns": [...],
  "filter": {
    "$all": [...],
    "$any": [...]
    ...
  },
  "sort": {
    "multiple": [...]
    ...
  },
  "page": {
    ...
  }
}

For usage, see also the API Guide.

Column selection

If the columns array is not specified, all columns are included. For link fields, only the ID column of the linked records is included in the response.

If the columns array is specified, only the selected and internal columns id and xata are included. The * wildcard can be used to select all columns.

For objects and link fields, if the column name of the object is specified, we include all of its sub-keys. If only some sub-keys are specified (via dotted notation, e.g. "settings.plan" ), then only those sub-keys from the object are included.

By the way of example, assuming two tables like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
{
  "tables": [
    {
      "name": "teams",
      "columns": [
        {
          "name": "name",
          "type": "string"
        },
        {
          "name": "owner",
          "type": "link",
          "link": {
            "table": "users"
          }
        },
        {
          "name": "foundedDate",
          "type": "datetime"
        },
      ]
    },
    {
      "name": "users",
      "columns": [
        {
          "name": "email",
          "type": "email"
        },
        {
          "name": "full_name",
          "type": "string"
        },
        {
          "name": "address",
          "type": "object",
          "columns": [
            {
              "name": "street",
              "type": "string"
            },
            {
              "name": "number",
              "type": "int"
            },
            {
              "name": "zipcode",
              "type": "int"
            }
          ]
        },
        {
          "name": "team",
          "type": "link",
          "link": {
            "table": "teams"
          }
        }
      ]
    }
  ]
}

A query like this:

1
2
3
4
5
6
7
POST /db/<dbname>:<branch>/tables/<table>/query
{
  "columns": [
    "name",
    "address.*"
  ]
}

returns objects like:

1
2
3
4
5
6
7
8
{
  "name": "Kilian",
  "address": {
    "street": "New street",
    "number": 41,
    "zipcode": 10407
  }
}

while a query like this:

1
2
3
4
5
6
7
POST /db/<dbname>:<branch>/tables/<table>/query
{
  "columns": [
    "name",
    "address.street"
  ]
}

returns objects like:

1
2
3
4
5
6
7
8
9
10
{
  "id": "id1"
  "xata": {
    "version": 0
  }
  "name": "Kilian",
  "address": {
    "street": "New street"
  }
}

If you want to return all columns from the main table and selected columns from the linked table, you can do it like this:

1
2
3
{
  "columns": ["*", "team.name"]
}

The "*" in the above means all columns, including columns of objects. This returns data like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
{
  "id": "id1"
  "xata": {
    "version": 0
  }
  "name": "Kilian",
  "email": "kilian@gmail.com",
  "address": {
    "street": "New street",
    "number": 41,
    "zipcode": 10407
  },
  "team": {
    "id": "XX",
    "xata": {
      "version": 0
    },
    "name": "first team"
  }
}

If you want all columns of the linked table, you can do:

1
2
3
{
  "columns": ["*", "team.*"]
}

This returns, for example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
{
  "id": "id1"
  "xata": {
    "version": 0
  }
  "name": "Kilian",
  "email": "kilian@gmail.com",
  "address": {
    "street": "New street",
    "number": 41,
    "zipcode": 10407
  },
  "team": {
    "id": "XX",
    "xata": {
      "version": 0
    },
    "name": "first team",
    "code": "A1",
    "foundedDate": "2020-03-04T10:43:54.32Z"
  }
}

Filtering

There are two types of operators:

• Operators that work on a single column: $is, $contains, $pattern, $includes, $gt, etc.
• Control operators that combine multiple conditions: $any, $all, $not , $none, etc.

All operators start with an $ to differentiate them from column names (which are not allowed to start with a dollar sign).

Exact matching and control operators

Filter by one column:

1
2
3
4
5
{
  "filter": {
    "<column_name>": "value"
  }
}

This is equivalent to using the $is operator:

1
2
3
4
5
6
7
{
  "filter": {
    "<column_name>": {
      "$is": "value"
    }
  }
}

For example:

1
2
3
4
5
{
  "filter": {
    "name": "r2"
  }
}

Or:

1
2
3
4
5
6
7
{
  "filter": {
    "name": {
      "$is": "r2"
    }
  }
}

For objects, both dots and nested versions work:

1
2
3
4
5
{
  "filter": {
    "settings.plan": "free"
  }
}

1
2
3
4
5
6
7
{
  "filter": {
    "settings": {
      "plan": "free"
    }
  }
}

If you want to OR together multiple values, you can use the $any operator with an array of values:

1
2
3
4
5
{
  "filter": {
    "settings.plan": { "$any": ["free", "paid"] }
  }
}

If you specify multiple columns in the same filter, they are logically AND'ed together:

1
2
3
4
5
6
{
  "filter": {
    "settings.dark": true,
    "settings.plan": "free"
  }
}

The above matches if both conditions are met.

To be more explicit about it, you can use $all or $any:

1
2
3
4
5
6
7
8
{
  "filter": {
    "$any": {
      "settings.dark": true,
      "settings.plan": "free"
    }
  }
}

The $all and $any operators can also receive an array of objects, which allows for repeating column names:

1
2
3
4
5
6
7
8
9
10
11
12
{
  "filter": {
    "$any": [
      {
        "name": "r1"
      },
      {
        "name": "r2"
      }
    ]
  }
}

You can check for a value being not-null with $exists:

1
2
3
4
5
{
  "filter": {
    "$exists": "settings"
  }
}

This can be combined with $all or $any :

1
2
3
4
5
6
7
8
9
10
11
12
{
  "filter": {
    "$all": [
      {
        "$exists": "settings"
      },
      {
        "$exists": "name"
      }
    ]
  }
}

Or you can use the inverse operator $notExists:

1
2
3
4
5
{
  "filter": {
    "$notExists": "settings"
  }
}

Partial match

$contains is the simplest operator for partial matching. Note that $contains operator can cause performance issues at scale, because indices cannot be used.

1
2
3
4
5
6
7
{
  "filter": {
    "<column_name>": {
      "$contains": "value"
    }
  }
}

Wildcards are supported via the $pattern operator:

1
2
3
4
5
6
7
{
  "filter": {
    "<column_name>": {
      "$pattern": "v*alu?"
    }
  }
}

The $pattern operator accepts two wildcard characters:

• * matches zero or more characters
• ? matches exactly one character

If you want to match a string that contains a wildcard character, you can escape them using a backslash (\). You can escape a backslash by usign another backslash.

You can also use the $endsWith and $startsWith operators:

1
2
3
4
5
6
7
8
9
10
{
  "filter": {
    "<column_name>": {
      "$endsWith": ".gz"
    },
    "<column_name>": {
      "$startsWith": "tmp-"
    }
  }
}

Numeric or datetime ranges

1
2
3
4
5
6
7
8
{
  "filter": {
    "<column_name>": {
      "$ge": 0,
      "$lt": 100
    }
  }
}

Date ranges support the same operators, with the date using the format defined in RFC 3339:

1
2
3
4
5
6
7
8
{
  "filter": {
    "<column_name>": {
      "$gt": "2019-10-12T07:20:50.52Z",
      "$lt": "2021-10-12T07:20:50.52Z"
    }
  }
}

The supported operators are $gt, $lt, $ge, $le.

Negations

A general $not operator can inverse any operation.

1
2
3
4
5
6
7
8
{
  "filter": {
    "$not": {
      "<column_name1>": "value1",
      "<column_name2>": "value1"
    }
  }
}

Note: in the above the two condition are AND together, so this does (NOT ( ... AND ...))

Or more complex:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
{
  "filter": {
    "$not": {
      "$any": [
        {
          "<column_name1>": "value1"
        },
        {
          "$all": [
            {
              "<column_name2>": "value2"
            },
            {
              "<column_name3>": "value3"
            }
          ]
        }
      ]
    }
  }
}

The $not: { $any: {}} can be shorted using the $none operator:

1
2
3
4
5
6
7
8
{
  "filter": {
    "$none": {
      "<column_name1>": "value1",
      "<column_name2>": "value1"
    }
  }
}

In addition, you can use operators like $isNot or $notExists to simplify expressions:

1
2
3
4
5
6
7
{
  "filter": {
    "<column_name>": {
      "$isNot": "2019-10-12T07:20:50.52Z"
    }
  }
}

Working with arrays

To test that an array contains a value, use $includesAny.

1
2
3
4
5
6
7
{
  "filter": {
    "<array_name>": {
      "$includesAny": "value"
    }
  }
}

includesAny

The $includesAny operator accepts a custom predicate that will check if any value in the array column matches the predicate. The $includes operator is a synonym for the $includesAny operator.

For example a complex predicate can include the $all , $contains and $endsWith operators:

1
2
3
4
5
6
7
8
9
10
11
12
{
  "filter": {
    "<array name>": {
      "$includes": {
        "$all": [
          { "$contains": "label" },
          { "$not": { "$endsWith": "-debug" } }
        ]
      }
    }
  }
}

includesNone

The $includesNone operator succeeds if no array item matches the predicate.

1
2
3
4
5
6
7
{
  "filter": {
    "settings.labels": {
      "$includesNone": [{ "$contains": "label" }]
    }
  }
}

The above matches if none of the array values contain the string "label".

includesAll

The $includesAll operator succeeds if all array items match the predicate.

Here is an example of using the $includesAll operator:

1
2
3
4
5
6
7
{
  "filter": {
    "settings.labels": {
      "$includesAll": [{ "$contains": "label" }]
    }
  }
}

The above matches if all array values contain the string "label".

Sorting

Sorting by one element:

1
2
3
4
5
6
POST /db/demo:main/tables/table/query
{
  "sort": {
    "index": "asc"
  }
}

or descendently:

1
2
3
4
5
6
POST /db/demo:main/tables/table/query
{
  "sort": {
    "index": "desc"
  }
}

Sorting by multiple fields:

1
2
3
4
5
6
7
8
9
10
11
POST /db/demo:main/tables/table/query
{
  "sort": [
    {
      "index": "desc"
    },
    {
      "createdAt": "desc"
    }
  ]
}

It is also possible to sort results randomly:

1
2
3
4
5
6
POST /db/demo:main/tables/table/query
{
  "sort": {
    "*": "random"
  }
}

Note that a random sort does not apply to a specific column, hence the special column name "*".

A random sort can be combined with an ascending or descending sort on a specific column:

1
2
3
4
5
6
7
8
9
10
11
POST /db/demo:main/tables/table/query
{
  "sort": [
    {
      "name": "desc"
    },
    {
      "*": "random"
    }
  ]
}

This will sort on the name column, breaking ties randomly.

Pagination

We offer cursor pagination and offset pagination. The cursor pagination method can be used for sequential scrolling with unrestricted depth. The offset pagination can be used to skip pages and is limited to 1000 records.

Example of cursor pagination:

1
2
3
4
5
6
POST /db/demo:main/tables/table/query
{
  "page": {
    "after":"fMoxCsIwFIDh3WP8c4amDai5hO5SJCRNfaVSeC9b6d1FD"
  }
}

In the above example, the value of the page.after parameter is the cursor returned by the previous query. A sample response is shown below:

1
2
3
4
5
6
7
8
9
{
  "meta": {
    "page": {
      "cursor": "fMoxCsIwFIDh3WP8c4amDai5hO5SJCRNfaVSeC9b6d1FD",
      "more": true
    }
  },
  "records": [...]
}

The page object might contain the follow keys, in addition to size and offset that were introduced before:

• after: Return the next page 'after' the current cursor
• before: Return the previous page 'before' the current cursor.
• start: Resets the given cursor position to the beginning of the query result set. Will return the first N records from the query result, where N is the page.size parameter.
• end: Resets the give cursor position to the end for the query result set. Returns the last N records from the query result, where N is the page.size parameter.

The request will fail if an invalid cursor value is given to page.before, page.after, page.start , or page.end. No other cursor setting can be used if page.start or page.end is set in a query.

If both page.before and page.after parameters are present we treat the request as a range query. The range query will return all entries after page.after, but before page.before, up to page.size or the maximum page size. This query requires both cursors to use the same filters and sort settings, plus we require page.after < page.before. The range query returns a new cursor. If the range encompass multiple pages the next page in the range can be queried by update page.after to the returned cursor while keeping the page.before cursor from the first range query.

The filter , columns, sort , and page.size configuration will be encoded with the cursor. The pagination request will be invalid if filter or sort is set. The columns returned and page size can be changed anytime by passing the columns or page.size settings to the next query.

In the following example of size + offset pagination we retrieve the third page of up to 100 results:

1
2
3
4
5
6
7
POST /db/demo:main/tables/table/query
{
  "page": {
    "size": 100,
    "offset": 200
  }
}

The page.size parameter represents the maximum number of records returned by this query. It has a default value of 20 and a maximum value of 200. The page.offset parameter represents the number of matching records to skip. It has a default value of 0 and a maximum value of 800.

Cursor pagination also works in combination with offset pagination. For example, starting from a specific cursor position, using a page size of 200 and an offset of 800, you can skip up to 5 pages of 200 records forwards or backwards from the cursor's position:

1
2
3
4
5
6
7
8
POST /db/demo:main/tables/table/query
{
  "page": {
    "size": 200,
    "offset": 800,
    "after": "fMoxCsIwFIDh3WP8c4amDai5hO5SJCRNfaVSeC9b6d1FD"
  }
}

Special cursors:

• page.after=end: Result points past the last entry. The list of records returned is empty, but page.meta.cursor will include a cursor that can be used to "tail" the table from the end waiting for new data to be inserted.
• page.before=end: This cursor returns the last page.
• page.start=<cursor>: Start at the beginning of the result set of the query. This is equivalent to querying the first page without a cursor but applying filter and sort . Yet the page.start cursor can be convenient at times as user code does not need to remember the filter, sort, columns or page size configuration. All these information are read from the cursor.
• page.end=<cursor>: Move to the end of the result set of the query. This is equivalent to querying the last page with page.before=end, filter, and sort . Yet the page.end cursor can be more convenient at times as user code does not need to remember the filter, sort, columns or page size configuration. All these information are read from the cursor.

When using special cursors like page.after="end" or page.before="end", we still allow filter and sort to be set.

Example of getting the last page:

1
2
3
4
5
6
7
POST /db/demo:main/tables/table/query
{
  "page": {
    "size": 10,
    "before": "end"
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
type QueryTable = {
    filter?: FilterExpression;
    sort?: SortExpression;
    page?: PageConfig;
    columns?: QueryColumnsProjection;
    /*
     * The consistency level for this request.
     *
     * @default strong
     */
    consistency?: "strong" | "eventual";
};

/**
 * @minProperties 1
 */
type FilterExpression = {
    $exists?: string;
    $existsNot?: string;
    $any?: FilterList;
    $all?: FilterList;
    $none?: FilterList;
    $not?: FilterList;
} & {
    [key: string]: FilterColumn;
};

type SortExpression = string[] | {
    [key: string]: SortOrder;
} | {
    [key: string]: SortOrder;
}[];

/**
 * Pagination settings.
 */
type PageConfig = {
    /*
     * Query the next page that follow the cursor.
     */
    after?: string;
    /*
     * Query the previous page before the cursor.
     */
    before?: string;
    /*
     * Query the first page from the cursor.
     */
    start?: string;
    /*
     * Query the last page from the cursor.
     */
    end?: string;
    /*
     * Set page size. If the size is missing it is read from the cursor. If no cursor is given Xata will choose the default page size.
     *
     * @default 20
     */
    size?: number;
    /*
     * Use offset to skip entries. To skip pages set offset to a multiple of size.
     *
     * @default 0
     */
    offset?: number;
};

type QueryColumnsProjection = (string | ProjectionConfig)[];

type FilterList = FilterExpression | FilterExpression[];

type FilterColumn = FilterColumnIncludes | FilterPredicate | FilterList;

type SortOrder = "asc" | "desc" | "random";

/**
 * A structured projection that allows for some configuration.
 */
type ProjectionConfig = {
    /*
     * The name of the column to project or a reverse link specification, see [API Guide](https://xata.io/docs/concepts/data-model#links-and-relations).
     */
    name?: string;
    columns?: QueryColumnsProjection;
    /*
     * An alias for the projected field, this is how it will be returned in the response.
     */
    as?: string;
    sort?: SortExpression;
    /*
     * @default 20
     */
    limit?: number;
    /*
     * @default 0
     */
    offset?: number;
};

/**
 * @maxProperties 1
 * @minProperties 1
 */
type FilterColumnIncludes = {
    $includes?: FilterPredicate;
    $includesAny?: FilterPredicate;
    $includesAll?: FilterPredicate;
    $includesNone?: FilterPredicate;
};

type FilterPredicate = FilterValue | FilterPredicate[] | FilterPredicateOp | FilterPredicateRangeOp;

type FilterValue = number | string | boolean;

/**
 * @maxProperties 1
 * @minProperties 1
 */
type FilterPredicateOp = {
    $any?: FilterPredicate[];
    $all?: FilterPredicate[];
    $none?: FilterPredicate | FilterPredicate[];
    $not?: FilterPredicate | FilterPredicate[];
    $is?: FilterValue | FilterValue[];
    $isNot?: FilterValue | FilterValue[];
    $lt?: FilterRangeValue;
    $le?: FilterRangeValue;
    $gt?: FilterRangeValue;
    $ge?: FilterRangeValue;
    $contains?: string;
    $startsWith?: string;
    $endsWith?: string;
    $pattern?: string;
};

/**
 * @maxProperties 2
 * @minProperties 2
 */
type FilterPredicateRangeOp = {
    $lt?: FilterRangeValue;
    $le?: FilterRangeValue;
    $gt?: FilterRangeValue;
    $ge?: FilterRangeValue;
};

type FilterRangeValue = number | string;

type QueryTable = {
    records: Record[];
    meta: RecordsMetadata;
};

/**
 * Xata Table Record Metadata
 */
type Record = RecordMeta & {
    [key: string]: any;
};

/**
 * Records metadata
 */
type RecordsMetadata = {
    page: {
        /*
         * last record id
         */
        cursor: string;
        /*
         * true if more records can be fetched
         */
        more: boolean;
        /*
         * the number of records returned per page
         */
        size: number;
    };
};

/**
 * Xata Table Record Metadata
 */
type RecordMeta = {
    id: RecordID;
    xata: {
        /*
         * The record's version. Can be used for optimistic concurrency control.
         */
        version: number;
        /*
         * The time when the record was created.
         */
        createdAt?: string;
        /*
         * The time when the record was last updated.
         */
        updatedAt?: string;
        /*
         * The record's table name. APIs that return records from multiple tables will set this field accordingly.
         */
        table?: string;
        /*
         * Highlights of the record. This is used by the search APIs to indicate which fields and parts of the fields have matched the search.
         */
        highlight?: {
            [key: string]: string[] | {
                [key: string]: any;
            };
        };
        /*
         * The record's relevancy score. This is returned by the search APIs.
         */
        score?: number;
        /*
         * Encoding/Decoding errors
         */
        warnings?: string[];
    };
};

/**
 * @maxLength 255
 * @minLength 1
 * @pattern [a-zA-Z0-9_-~:]+
 */
type RecordID = string;

type QueryTable = {
    id?: string;
    message: string;
};

{
  "message": "invalid API key"
}

type QueryTable = {
    id?: string;
    message: string;
};

type QueryTable = {
    id?: string;
    message: string;
};