Filtering records
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.
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.
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();
records = xata.data().query("Users", {
"filter": {
"name": "Keanu Reeves"
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE name = $1 LIMIT 20;",
"params": ["Keanu Reeves"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"name": "Keanu Reeves"
}
}
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();
records = xata.data().query("Users", {
"filter": {
"name": { "$is": "Keanu Reeves" }
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE name = $1 LIMIT 20;",
"params": ["Keanu Reeves"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"name": { "$is": "Keanu Reeves" }
}
}
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();
records = xata.data().query("Users", {
"filter": {
"name": {
"$any": ["Keanu Reeves", "Carrie-Anne Moss"]
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE name IN ($1,$2) LIMIT 20;",
"params": ["Keanu Reeves", "Carrie-Ann Moss"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"name": {
"$any": ["Keanu Reeves", "Carrie-Anne Moss"]
}
}
}
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();
records = xata.data().query("Users", {
"filter": {
"name": "Keanu Reeves",
"city": "New York"
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE name = $1 AND city = $2 LIMIT 20;",
"params": ["Keanu Reeves", "New York"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"name": "Keanu Reeves",
"city": "New York"
}
}
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();
records = xata.data().query("Users", {
"filter": {
"$any": {
"name": "Keanu Reeves",
"city": "New York"
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE name = $1 OR city = $2 LIMIT 20;",
"params": ["Keanu Reeves", "New York"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"$any": {
"name": "Keanu Reeves",
"city": "New York"
}
}
}
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();
records = xata.data().query("Users", {
"filter": {
"$any": [{ "name": "Keanu Reeves" }, { "name": "Carrie-Anne Moss" }]
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE name = $1 OR name = $2 LIMIT 20;",
"params": ["Keanu Reeves", "Carrie-Ann Moss"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"$any": [{ "name": "Keanu Reeves" }, { "name": "Carrie-Anne Moss" }]
}
}
You can check for a value in a column being not-null with $exists
:
const records = await xata.db.Users.filter({
$exists: 'name'
}).getMany();
records = xata.data().query("Users", {
"filter": {
"$exists": "name"
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE name IS NOT NULL LIMIT 20;"
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"$exists": "name"
}
}
This can be combined with $all
or $any
:
const records = await xata.db.Users.filter({
$all: [{ $exists: 'name' }, { $exists: 'address' }]
}).getMany();
records = xata.data().query("Users", {
"filter": {
"$all": [{ "$exists": "name" }, { "$exists": "address" }]
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE (name IS NOT NULL) AND (address IS NOT NULL) LIMIT 20;"
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"$all": [{ "$exists": "name" }, { "$exists": "address" }]
}
}
Or you can use the inverse operator $notExists
:
const records = await xata.db.Users.filter({
$notExists: 'name'
}).getMany();
records = xata.data().query("Users", {
"filter": {
"$notExists": "name"
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE name IS NULL LIMIT 20;"
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"$notExists": "name"
}
}
The $not
operator can inverse any operation.
const records = await xata.db.Users.filter({
$not: {
name: "Keanu Reeves"
}
}).getMany();
records = xata.data().query("Users", {
"filter": {
"$not": {
"name": "Keanu Reeves"
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE NOT name = $1 LIMIT 20;",
"params": ["Keanu Reeves"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"$not": {
"name": "Keanu Reeves"
}
}
}
Or more complex:
const records = await xata.db.Users.filter({
$not: {
$any: [
{
name: 'Keanu Reeves'
},
{
name: 'Laurence Fishburne'
}
]
}
}).getMany();
records = xata.data().query("Users", {
"filter": {
"$not": {
"$any": [
{
"name": "Keanu Reeves"
},
{
"name": "Laurence Fishburne"
}
]
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE NOT (name = $1 OR name = $2) LIMIT 20;"
"params": ["Keanu Reeves", "Laurence Fishburne"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"$not": {
"$any": [
{
"name": "Keanu Reeves"
},
{
"name": "Laurence Fishburne"
}
]
}
}
}
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();
records = xata.data().query("Users", {
"filter": {
"$none": {
"name": "Keanu Reave",
"city": "New York"
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE name NOT IN ($1,$2) LIMIT 20;",
"params": ["Keanu Reeves", "Laurence Fishburn"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"$none": {
"name": "Keanu Reave",
"city": "New York"
}
}
}
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();
records = xata.data().query("Users", {
"filter": {
"name": {
"$isNot": "Keanu Reave"
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE NOT name = $1 LIMIT 20;",
"params": ["Keanu Reeves"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"name": {
"$isNot": "Keanu Reave"
}
}
}
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();
records = xata.data().query("Users", {
"filter": {
"name": {
"$contains": "Keanu"
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE name LIKE $1 LIMIT 20;",
"params": ["%Keanu Reeves%"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"name": {
"$contains": "Keanu"
}
}
}
Wildcards are supported via the $pattern
operator:
const records = await xata.db.Users.filter({
name: { $pattern: 'K*an? R*' }
}).getMany();
records = xata.data().query("Users", {
"filter": {
"name": {
"$pattern": "K*an? R*"
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE name ~ $1 LIMIT 20;",
"params": ["K.*an.? R.*"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"name": {
"$pattern": "K*an? R*"
}
}
}
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();
records = xata.data().query("Users", {
"filter": {
"name": {
"$startsWith": "Keanu"
}
}
})
# or
records = xata.data().query("Users", {
"filter": {
"name": {
"$endsWith": "Reeves"
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE name ~ $1 LIMIT 20;",
"params": ["^Keanu"]
}
// or
{
"statement": "SELECT * FROM \"Users\" WHERE name ~ $1 LIMIT 20;",
"params": ["Reeves$"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"name": {
"$startsWith": "Keanu"
}
}
}
// or
{
"filter": {
"name": {
"$endsWith": "Reeves"
}
}
}
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();
records = xata.data().query("Users", {
"filter": {
"name": {
"$iContains": "keanu"
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE name ILIKE $1 LIMIT 20;",
"params": ["%keanu reeves%"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"name": {
"$iContains": "keanu"
}
}
}
Example for $iPattern
:
const records = await xata.db.Users.filter({
name: { $iPattern: 'k*an? r*' }
}).getMany();
records = xata.data().query("Users", {
"filter": {
"name": {
"$iPattern": "K*an? R*"
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Users\" WHERE name ILIKE $1 LIMIT 20;",
"params": ["K.*an.? R.*"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"name": {
"$iPattern": "K*an? R*"
}
}
}
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();
records = xata.data().query("Posts", {
"filter": {
"views": {
"$gt": 5
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Posts\" WHERE views > 5 LIMIT 20;"
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"views": {
"$gt": 5
}
}
}
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();
records = xata.data().query("Posts", {
"filter": {
"$all": [
{
"xata.createdAt": {
"$ge": "2022-10-25T01:00:00.000Z"
}
},
{
"xata.createdAt": {
"$lt": "2022-10-25T02:00:00.000Z"
}
}
]
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Posts\" WHERE \"xata.createdAt\"::timestamptz < $1 AND \"xata.createdAt\"::timestamptz >= $2 LIMIT 20;",
"params": ["2022-10-25T02:00:00.000Z", "2022-10-25T01:00:00.000Z"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"$all": [
{
"xata.createdAt": {
"$ge": "2022-10-25T01:00:00.000Z"
}
},
{
"xata.createdAt": {
"$lt": "2022-10-25T02:00:00.000Z"
}
}
]
}
}
This section applies to the multiple
type, because it's currently the only array type in Xata.
This feature is not supported in queries made to our search and analytics store. It applies to queries that use the
aggregate
, search
(both keyword and vector) endpoints.
To test that an array contains a value, use the $includes
operator:
const records = await xata.db.Posts.filter({
labels: { $includes: "matrix" },
}).getMany();
records = xata.data().query("Posts", {
"filter": {
"labels": {
"$includes": "matrix"
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Posts\" WHERE 'matrix' = ANY (labels);"
}
// Multiple values
{
"statement": "SELECT * FROM \"Posts\" WHERE ARRAY['matrix','lotr'] <@ labels;"
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"labels": {
"$includes": "matrix"
}
}
}
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();
records = xata.data().query("Posts", {
"filter": {
"labels": {
"$includes": {
"$contains": "mat"
}
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Posts\" WHERE array_to_string(labels,'') LIKE '%mat%';"
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"labels": {
"$includes": {
"$contains": "mat"
}
}
}
}
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();
records = xata.data().query("Posts", {
"filter": {
"labels": {
"$includesAll": {
"$contains": "mat"
}
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Posts\" WHERE cardinality(\"labels\") = (SELECT sum((label LIKE $1) :: int) FROM unnest(labels) AS label);"
"params": ["%mat%"]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"labels": {
"$includesAll": {
"$contains": "mat"
}
}
}
}
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();
records = xata.data().query("Posts", {
"filter": {
"labels": {
"$includesNone": {
"$contains": "act"
}
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Posts\" WHERE NOT array_to_string(labels,'') LIKE '%mat%';"
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"labels": {
"$includesNone": {
"$contains": "act"
}
}
}
}
The above matches if none of the labels contains the string "act".
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();
records = xata.data().query("Posts", {
"filter": {
"social->linkedin->shared": {
"$gt": 5
}
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"Posts\" WHERE (social->'linkedin'->>'shared')::numeric > 5;"
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"social->linkedin->shared": {
"$gt": 5
}
}
}
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.
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();
records = xata.data().query("access_logs", {
"filter": {
"$all": [
{ "status_code": { "$is": 502 } },
{ "method": { "$is": "DELETE" } },
]
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"access_logs\" WHERE (status_code = 502) AND (method = \"DELETE\") LIMIT 20;"
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"$all": [{ "status_code": { "$is": 502 } }, { "method": { "$is": "DELETE" } }]
}
}
This example demonstrates how to use multiple operators. You want to get all records that:
- IP range starts with
202.8.*
or29.171.152.*
- Attempted to log in on URL path
usr/login
- HTTP methods are
PUT
orPOST
- 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();
records = xata.data().query("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 },
}
}
]
}
})
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
{
"statement": "SELECT * FROM \"access_logs\" WHERE ((ip ~ $1) OR (ip ~ $2)) AND (method IN ($2, $3)) AND (url_path = $4) AND (status_code != $5) LIMIT 20;"
"params": ["202.8.", "29.171.152.", "PUT", "POST", "usr/login", 200]
}
// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/query
{
"filter": {
"$all": [
{
"$any": [{ "ip": { "$startsWith": "202.8." } }, { "ip": { "$startsWith": "29.171.152." } }]
},
{ "method": { "$any": ["PUT", "POST"] } },
{ "url_path": { "$is": "usr/login" } },
{
"$not": {
"status_code": { "$is": 200 }
}
}
]
}
}