Filters

Filters allow only rows that meet specific criteria to be returned.

Filters can be used for select(), update(), upsert(), and delete() queries.

eq

Only matches rows where the column value equals the specified value.

To check whether a column value is NULL, use .is() instead of eq.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	any	Required	Value for filtering

Code Example

// Query all records from the articles table where title equals "Tencent CloudBase"
const { data, error } = await db
  .from("articles")
  .select()
  .eq("title", "Tencent CloudBase");

neq

Only matches rows where the column value does not equal the specified value.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	any	Required	Value for filtering

Code Example

// Query all records from the articles table where title is not equal to "Tencent CloudBase"
const { data, error } = await db
  .from("articles")
  .select()
  .neq("title", "Tencent CloudBase");

gt

Only matches rows where the column value is greater than the specified value.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	any	Required	Value for filtering

Code Example

// Query all records from the articles table where id is greater than 2
const { data, error } = await db.from("articles").select().gt("id", 2);

gte

Only matches rows where the column value is greater than or equal to the specified value.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	any	Required	Value for filtering

Code Example

// Query all records from the articles table where id is greater than or equal to 2
const { data, error } = await db.from("articles").select().gte("id", 2);

lt

Only matches rows where the column value is less than the specified value.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	any	Required	Value for filtering

Code Example

// Query all records from the articles table where id is less than 2
const { data, error } = await db.from("articles").select().lt("id", 2);

lte

Only matches rows where the column value is less than or equal to the specified value.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	any	Required	Value for filtering

Code Example

// Query all records from the articles table where id is less than or equal to 2
const { data, error } = await db.from("articles").select().lte("id", 2);

like

Only matches rows where the column value matches a specific pattern (case sensitivity depends on collation rules).

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
pattern	string	Required	Pattern to match

Code Example

// Query all records from the articles table where title contains "cloudbase"
const { data, error } = await db
  .from("articles")
  .select()
  .like("title", "%cloudbase%");

is

Only matches rows where the column value equals the specified value.

For non-boolean columns, it is mainly used to check whether the column value is NULL.

For boolean columns, it can also be set to true or false, behaving the same as .eq().

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
value	Object	Required	Value for filtering

Code Example

// Query all records from the articles table where title is null
const { data, error } = await db.from("articles").select().is("title", null);

in

Only matches rows where the column value is contained in the specified array.

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
values	Array	Required	Array of values to filter

Code Example

// Query all records from the articles table where title is in the specified array ["Tencent CloudBase", "CloudBase"]
const { data, error } = await db
  .from("articles")
  .select()
  .in("title", ["Tencent CloudBase", "CloudBase"]);

match

Only matches rows where every column in the query key equals its associated value, equivalent to a shorthand for multiple .eq() calls.

Parameters

Parameter	Type	Required	Description
query	Record<ColumnName, Row['ColumnName']>	Required	Filter object, column names as keys mapped to their filter values

Code Example

// Query all records from the articles table where id equals 2 and title equals "Tencent CloudBase"
const { data, error } = await db
  .from("articles")
  .select()
  .match({ id: 2, title: "Tencent CloudBase" });

not

Only matches rows that do not satisfy the filter condition.

Unlike most filters, the operator and value are used as-is and must follow PostgreSQL syntax. You also need to ensure they are properly escaped.

not() expects raw PostgreSQL syntax as the filter value.

.not('id', 'in', '(5,6,7)')  // Use `()` for `in` filter
.not('name', 'like', '%test%')  // Use `not like` for pattern matching

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
operator	string	Required	Filter operator to negate, following PostgreSQL syntax
value	any	Required	Filter value, following PostgreSQL syntax

Code Example

// Query all records from the articles table where title is not null
const { data, error } = await db
  .from("articles")
  .select()
  .not("title", "is", null);

or

Only matches rows that satisfy at least one filter condition.

Unlike most filters, the filters are used as-is and must follow PostgreSQL syntax. You also need to ensure they are properly escaped.

Currently, it is not possible to perform .or() filtering across multiple tables.

or() expects raw PostgreSQL syntax as filter names and values.

.or('id.in.(5,6,7), name.like.%test%')  // Use `()` for `in` filter, use `like` and `%` for pattern matching
.or('id.in.(5,6,7), name.not.like.%test%')  // Use `not.like` for negated pattern matching

Parameters

Parameter	Type	Required	Description
filters	string	Required	Filters to use, following PostgreSQL syntax
options	object	Required	Named parameters
options.referencedTable	string	Optional	Set to filter on the referenced table instead of the parent table

Code Example

// Query all records from the articles table where id equals 2 or title equals "Tencent CloudBase"
const { data, error } = await db
  .from("articles")
  .select()
  .or(`id.eq.2,title.eq.Tencent CloudBase`);

filter

Only matches rows that satisfy the filter condition. This is an escape hatch - you should use specific filter methods whenever possible.

Unlike most filters, the operator and value are used as-is and must follow PostgreSQL syntax. You also need to ensure they are properly escaped.

filter() expects raw PostgreSQL syntax as the filter value.

.filter('id', 'in', '(5,6,7)')  // Use `()` for `in` filter
.filter('name', 'like', '%test%')  // Use `like` for pattern matching
.filter('name', 'not.like', '%test%')  // Use `not.like` for negated pattern matching

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
operator	string	Required	Filter operator, following PostgreSQL syntax
value	any	Required	Filter value, following PostgreSQL syntax

Code Example

// Query records where title is in the specified value list
// Query all records from the articles table where title is in the specified value list ["Tencent CloudBase", "CloudBase"]
const { data, error } = await db
  .from("articles")
  .select()
  .filter("title", "in", "(Tencent CloudBase,CloudBase)");

// Filter on a referenced table
// Query all records from the articles table where the associated categories.name equals "Technology"
const { data, error } = await db
  .from("articles")
  .select()
  .filter("categories.name", "eq", "Technology");