Command line interface
Dedicated clusters
Migrations
PR based workflow
Usage metrics
Installation & API keys
Getting records
Creating records
Updating records
Deleting records
Filtering records
Transactions
Searching records
Vector searching records
Aggregations
Summarize
File attachments
Image transformations
CSV
Auth JS
Cloudflare Workers
LangChain
Available regions
Pricing
Service level agreement
Limitations
Branch inactivity
Support
Docs contribution guide

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: 'Reeves' }
}).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();

#

Examples

The examples provided illustrate how to use various filtering options and operators in different use cases. In this example, use the web server's access log, which is stored in a table named access_logs.

ip,timestamp,method,url_path,version,status_code,bytes,referrer,user_agent,response_time
233.223.117.90,27/Dec/2037:12:00:00 +0530,DELETE,usr/admin,HTTP/1.0,502,4963,-,Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML; like Gecko) Chrome/89.0.4380.0 Safari/537.36 Edg/89.0.759.0,45
162.253.4.179,27/Dec/2037:12:00:00 +0530,GET,usr/admin/developer,HTTP/1.0,200,5041,http://www.parker-miller.org/tag/list/list/privacy/,Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML; like Gecko) Chrome/87.0.4280.141 Safari/537.36,3885
252.156.232.172,27/Dec/2037:12:00:00 +0530,POST,usr/register,HTTP/1.0,404,5028,-,Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML; like Gecko) Chrome/87.0.4280.88 Safari/537.36 OPR/73.0.3856.329,3350
182.215.249.159,27/Dec/2037:12:00:00 +0530,PUT,usr/register,HTTP/1.0,304,4936,http://www.parker-miller.org/tag/list/list/privacy/,Mozilla/5.0 (Android 10; Mobile; rv:84.0) Gecko/84.0 Firefox/84.0,767
160.36.208.51,27/Dec/2037:12:00:00 +0530,POST,usr,HTTP/1.0,304,4979,http://www.parker-miller.org/tag/list/list/privacy/,Mozilla/5.0 (Linux; Android 10; ONEPLUS A6000) AppleWebKit/537.36 (KHTML; like Gecko) Chrome/86.0.4240.198 Mobile Safari/537.36 OPR/61.2.3076.56749,84
255.231.52.33,27/Dec/2037:12:00:00 +0530,PUT,usr/admin/developer,HTTP/1.0,403,5054,http://www.parker-miller.org/tag/list/list/privacy/,Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML; like Gecko) Chrome/87.0.4280.88 Safari/537.36 OPR/73.0.3856.329,3629
238.217.83.154,27/Dec/2037:12:00:00 +0530,DELETE,usr/register,HTTP/1.0,304,5152,-,Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML; like Gecko) Chrome/89.0.4380.0 Safari/537.36 Edg/89.0.759.0,4679
119.170.1.203,27/Dec/2037:12:00:00 +0530,GET,usr,HTTP/1.0,303,5011,http://www.parker-miller.org/tag/list/list/privacy/,Mozilla/5.0 (Linux; Android 10; ONEPLUS A6000) AppleWebKit/537.36 (KHTML; like Gecko) Chrome/87.0.4280.141 Mobile Safari/537.36,4541
59.107.116.6,27/Dec/2037:12:00:00 +0530,POST,usr,HTTP/1.0,502,5008,-,Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_3) AppleWebKit/537.75.14 (KHTML; like Gecko) Version/7.0.3 Safari/7046A194A,1213

The dataset, available on Kaggle, consists of server logs and is licensed under the public domain. In this example, the data has been trimmed to 1000 records and converted to a CSV for a convenient import. The file can be downloaded here.

#

Multiple exact matches

In the upcoming query, you'll retrieve all records that have a status_code of exactly 502 and use the HTTP method DELETE. To combine both filter conditions, you must use the $all operator.

const records = await xata.db.access_logs
  .filter({
    $all: [{ status_code: { $is: 502 } }, { method: { $is: 'DELETE' } }]
  })
  .getMany();

#

Nested operators

This example demonstrates how to use multiple operators. You want to get all records that:

  • IP range starts with 202.8.* or 29.171.152.*
  • Attempted to log in on URL path usr/login
  • HTTP methods are PUT or POST
  • Did not produce the status code 200
const records = await xata.db.access_logs
  .filter({
    $all: [
      {
        // IP range starts with 202.8.* or 29.171.152.*
        $any: [{ ip: { $startsWith: '202.8.' } }, { ip: { $startsWith: '29.171.152.' } }]
      },
      // Attempted to log in with PUT or POST
      { method: { $any: ['PUT', 'POST'] } },
      // Log in url_path exact match
      { url_path: { $is: 'usr/login' } },
      {
        // Did not produce the status code 200
        $not: {
          status_code: { $is: 200 }
        }
      }
    ]
  })
  .getMany();

On this page

OperatorsExact matchingControl operatorsNegationsPartial matchCase insensitive matchingComparison operatorsWorking with arrays / multiple typeWorking with JSON documentsExamplesMultiple exact matchesNested operators