Filter

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

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

eq

Only rows whose column values equal the specified value are matched.

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 Cloud Development"
const { data, error } = await db
  .from("articles")
  .select()
  .eq("title", "Tencent Cloud Development");

neq

Only rows whose column values do not equal the specified value are matched.

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 Cloud Development".
const { data, error } = await db
  .from("articles")
  .select()
  .neq("title", "Tencent Cloud Development");

gt

Only rows whose column values are greater than the specified value are matched.

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 > 2
const { data, error } = await db.from("articles").select().gt("id", 2);

gte

Only rows whose column values are greater than or equal to the specified value are matched.

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 >= 2
const { data, error } = await db.from("articles").select().gte("id", 2);

lt

Only rows whose column values are less than the specified value are matched.

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 < 2
const { data, error } = await db.from("articles").select().lt("id", 2);

lte

Only rows whose column values are less than or equal to the specified value are matched.

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 <= 2
const { data, error } = await db.from("articles").select().lte("id", 2);

like

Only match rows where column values match a specific pattern (case sensitivity is constrained by validation 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 rows whose column values equal the specified value are matched.

For non-Boolean columns, it is primarily used to check whether column values are 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 rows whose column values are contained in the specified array are matched.

Parameters

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

Code Example

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

match

Only matches rows where each column in the query key equals its associated value, equivalent to a shorthand form of multiple .eq().

Parameters

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

Code Example

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

not

Matches only rows that do not satisfy the filter condition.

Unlike most filters, operators and values are used as is, must follow MySQL syntax, and must be properly escaped.

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

.not('id', 'in', '(5,6,7)')  // Use `()` for `in` filters
.not('name', 'like', '%test%')  // Performs fuzzy matching using `not like`

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
operator	string	Required	Filter operator to negate, follows MySQL syntax
value	any	Required	Filter value, follows MySQL 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

Matches rows that satisfy at least one filter condition.

Unlike most filters, filters are used as is, must follow MySQL syntax, and must be properly escaped.

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

The .or() method expects the raw MySQL syntax as the filter name and value.

.or('id.in.(5,6,7), name.like.%test%')  // Uses `()` for `in` filters and `%` for fuzzy matching with `like`
.or('id.in.(5,6,7), name.not.like.%test%')  // Use `not.like` for inverse fuzzy matching

Parameters

Parameter	Type	Required	Description
filters	string	Required	Filters to use, follows MySQL syntax
options	object	Required	Named parameters
options.referencedTable	string	Optional	Set to filter the referenced table rather than the parent table

Code Example

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

filter

Only rows that meet the filter conditions are matched; this is an "escape hatch" and specific filter methods should be used whenever possible.

Unlike most filters, operators and values are used as is, must follow MySQL syntax, and must be properly escaped.

The filter() method expects the raw MySQL syntax as the filter value.

.filter('id', 'in', '(5,6,7)')  // Use `()` for `in` filters
.filter('name', 'like', '%test%')  // Performs fuzzy matching using `like`
.filter('name', 'not.like', '%test%')  // Performs reverse fuzzy matching using `not.like`

Parameters

Parameter	Type	Required	Description
column	string	Required	Column to filter
operator	string	Required	Filter operator, follows MySQL syntax
value	any	Required	Filter value, follows MySQL 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 Cloud Development", "Cloud Development"]
const { data, error } = await db
  .from("articles")
  .select()
  .filter("title", "in", "(Tencent Cloud Development, Cloud Development)");

// Filter on the reference 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");