Installation & API keys
Branching workflow
CLI
Available regions
Serverless data platform
How Xata works
Data model
User model
Schema
Data copying
Migration files
File attachments
Pricing
Getting records
Creating records
Updating records
Deleting records
Searching records
Vector searching records
Filtering records
Aggregations
Summarize
Transactions
SQL
Ask AI
File attachments
Image transformations
Overview
Authentication
API contexts
REST API limits
Airbyte
Auth JS
Drizzle
GitHub
Kysely
LangChain
Netlify
OAuth 2
Vercel
Visual Studio Code
Zapier
Create schema from code
Xata examples
Gallery app
Tupu.io
Xata ChatGPT
XMDb

Filtering records

Edit on GitHub

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 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.

To distinguish operators from column names, operators are prefixed with a $ symbol, whereas column names cannot begin with a dollar sign.

#

Exact matching

Filter by one column having a particular value:

{
  "filter": {
    "<column_name>": "value"
  }
}

Example:

const records = await xata.db.Users.filter({
  name: 'Keanu Reeves'
}).getMany();
 
// or
const records = await xata.db.Users.filter('name', 'Keanu Reave').getMany();

This is equivalent to using the $is operator:

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

#

Control operators

To combine multiple values using the OR logic, you can use the $any operator by providing an array of values:

const records = await xata.db.Users.filter({
  name: { $any: ['Keanu Reeves', 'Carrie-Anne Moss'] }
}).getMany();

When you include multiple columns within the same filter, they are logically combined using the AND operator:

const records = await xata.db.Users.filter({
  name: 'Keanu Reeves',
  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:

const records = await xata.db.Users.filter({
  $any: {
    name: 'Keanu Reeves',
    city: 'New York'
  }
}).getMany();

The $all and $any operators can also accept an array of objects, enabling the use of repeated column names:

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

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

const records = await xata.db.Users.filter({
  $exists: 'name'
}).getMany();

This can be combined with $all or $any:

const records = await xata.db.Users.filter({
  $all: [{ $exists: 'name' }, { $exists: 'address' }]
}).getMany();

Or you can use the inverse operator $notExists:

const records = await xata.db.Users.filter({
  $notExists: 'name'
}).getMany();

#

Negations

The $not operator can inverse any operation.

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

Or more complex:

const records = await xata.db.Users.filter({
  $not: {
    $any: [
      {
        name: 'Keanu Reeves'
      },
      {
        name: 'Laurence Fishburne'
      }
    ]
  }
}).getMany();

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

const records = await xata.db.Users.filter({
  $none: {
    name: 'Keanu Reave',
    city: 'New York'
  }
}).getMany();

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

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.

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:

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:

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

#

Case insensitive matching

The $contains and $pattern operations are case sensitive but they have case insensitive counterparts: $iContains and $iPattern. $iContains finds case-insensitive substrings, while $iPattern performs case-insensitive pattern matching, useful for regular expressions or wildcard searches.

Their usage is the same as the case sensitive versions.

Example for $iContains:

const records = await xata.db.Users.filter({
  name: { $iContains: 'Keanu' }
}).getMany();
 
// or
const records = await xata.db.Users.filter('name', iContains('keanu')).getMany();

Example for $iPattern:

const records = await xata.db.Users.filter({
  name: { $iPattern: 'k*an? r*' }
}).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:

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

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

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:

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.

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:

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.

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

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

#

Working with JSON documents

Any node of a JSON document tree is easily accessible via the -> operator. We can use this notation to apply most of the operators we've seen so far in order to filter JSON documents. Following our previous examples with a Posts table, let's assume that each post has a social field of type JSON which contains the times the post was shared on different social networks:

{
  "id": "1",
  "title": "My first post",
  "social": {
    "x": {
      "shared": 1
    },
    "facebook": {
      "shared": 51
    },
    "linkedin": {
      "shared": 204
    }
  }
}

To filter posts based on LinkedIn sharing metrics, use the following:

const records = await xata.db.Posts.filter({
  'social->linkedin->shared': { $gt: 5 }
}).getMany();

On this page

OperatorsExact matchingControl operatorsNegationsPartial matchCase insensitive matchingComparison operatorsWorking with arrays / multiple typeWorking with JSON documents