Want to find the answer quick?

Filtering

The same filtering syntax can be used in multiple places, including the Query API, the Search API, and the Aggregate API. By using the same syntax, you can more easily switch between the APIs that you need.

We recommend that you use the web UI to build your filters, then use the Get Code Snippet functionality to get the code for your programming language.

Operators

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

Filter by one column having a particular value:

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

Example:


1
2
3
4
5
6
7
const records = await xata.db.Users.filter({
  name: "Keanu Reave",
}).getMany();

// or
const records = await xata.db.Users
  .filter("name", "Keanu Reave").getMany();

This is equivalent to using the $is operator:


1
2
3
4
5
6
7
8
const records = await xata.db.Users.filter({
  name: { $is: "Keanu Reave" },
}).getMany();

//or
import { is } from "@xata.io/client";
const records = await xata.db.Users
  .filter("name", is("Keanu Reave")).getMany();

For objects, both the dotted-notation and nested objects work and are equivalent:


1
2
3
4
5
6
7
8
9
10
const records = await xata.db.Users.filter({
    "address.city": "New York"
}).getMany()

// or
const records = await xata.db.Users.filter({
  "address": {
      "city": "New York"
  }
}).getMany()

Control operators

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


1
2
3
const records = await xata.db.Users.filter({
  name: { $any: ["Keanu Reave", "Carrie-Anne Moss"] },
}).getMany();

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


1
2
3
4
const records = await xata.db.Users.filter({
  name: "Keanu Reave",
  "address.city": "New York",
}).getMany();

The above matches if both conditions are met. You can be more explicit about it by using the $all and $any operators:


1
2
3
4
5
6
const records = await xata.db.Users.filter({
  $any: {
    name: "Keanu Reave",
    "address.city": "New York",
  },
}).getMany();

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


1
2
3
const records = await xata.db.Users.filter({
  $any: [{ name: "Keanu Reave" }, { name: "Carrie-Anne Moss" }],
}).getMany();

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


1
2
3
const records = await xata.db.Users.filter({
  $exists: "name"
}).getMany();

This can be combined with $all or $any:


1
2
3
4
5
6
const records = await xata.db.Users.filter({
  $all: [
    { $exists: "name" },
    { $exists: "address" },
  ],
}).getMany();

Or you can use the inverse operator $notExists:


1
2
3
const records = await xata.db.Users.filter({
  $notExists: "name"
}).getMany();

Negations

The $not operator can inverse any operation.


1
2
3
4
5
const records = await xata.db.Users.filter({
  $not: {
    name: "Keanu Reeves"
  }
}).getMany();

Or more complex:


1
2
3
4
5
6
7
8
9
const records = await xata.db.Users.filter({
    $not: {
        $any: [{
            name: "Keany Reave"
        }, {
            name: "Laurence Fishburne"
        }]
    }
}).getMany();

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


1
2
3
4
5
6
const records = await xata.db.Users.filter({
  $none: {
    "name": "Keanu Reave",
    "address.city": "New York"
  }
}).getMany();

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


1
2
3
const records = await xata.db.Users.filter({
  name: { $isNot: "Keanu Reave" },
}).getMany();

Partial match

Xata supports several operators for partial matching, but you should consider using the Search API for more powerful full-text-search.

To match a value partially, you can use the $contains operator. Note that $contains operator can cause performance issues at scale, because indices cannot be used.


1
2
3
4
5
6
7
const records = await xata.db.Users.filter({
  name: { $contains: "Keanu" },
}).getMany();

// or
const records = await xata.db.Users
  .filter("name", contains("Keanu")).getMany();

Wildcards are supported via the $pattern operator:


1
2
3
const records = await xata.db.Users.filter({
  name: {$pattern: "K*an? R*"}
}).getMany();

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 using another backslash.

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


1
2
3
4
5
6
7
8
const records = await xata.db.Users.filter({
  name: {$startsWith: "Keanu"}
}).getMany();

// or
const records = await xata.db.Users.filter({
  name: {$endsWith: "Reave"}
}).getMany();

Comparison operators

The comparison operators are:

$gt - greater than
$ge - greater than or equal
$lt - less than
$le - less than or equal

These operators work on strings, numbers, and datetimes. For example:


1
2
3
const records = await xata.db.Posts.filter({
  views: {$gt: 5}
}).getMany();

Example with date ranges, using the built-in Xata timestamp columns:


1
2
3
4
5
6
7
8
9
10
const records = await xata.db.Posts.filter({
  $all: [
    {
      xata.createdAt: { $ge: new Date("2022-10-25T01:00:00Z") },
    },
    {
      xata.createdAt: { $lt: new Date("2022-10-25T02:00:00Z") },
    }
  ],
}).getMany();

Working with arrays / multiple type

This section applies to the multiple type, because it's currently the only array type in Xata.

To test that an array contains a value, use the $includes operator:


1
2
3
const records = await xata.db.Posts.filter({
  labels: { $includes: "matrix" },
}).getMany();

The $includes operator accepts a custom predicate that will check if any array values matches the predicate.


1
2
3
const records = await xata.db.Posts.filter({
  labels: { $includes: { $contains: "mat" } },
}).getMany();

The above matches if any of the labels contain the string "mat". You can also check that all values in the array match the predicate, via the $includesAll operator:


1
2
3
const records = await xata.db.Posts.filter({
  labels: { $includesAll: [{ $contains: "mat" }] },
}).getMany();

The above matches if all of the labels contain the string "mat".

Finally, an $includesNone operator exists which matches if none of the values in the array match the predicate.


1
2
3
const records = await xata.db.Posts.filter({
  labels: { $includesNone: [{ $contains: "act" }] },
}).getMany();

The above matches if none of the labels contains the string "act".

On this page