Filter

The filter returns a set of rows that match specific conditions.

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

eq

Return only the rows where the column value is equal to the specified value.

To check whether a column value is NULL, use .is() rather than eq.

Parameter

Parameter	Type	Required	Description
column	string	required	column to be filtered
value	any	required	filtered value

Sample Code

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

neq

Return only the rows where the column value does not match the specified value.

Parameter

Parameter	Type	Required	Description
column	string	required	column to be filtered
value	any	required	filtered value

Sample Code

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

gt

Return only the rows where the column value is larger than the specified value.

Parameter

Parameter	Type	Required	Description
column	string	required	column to be filtered
value	any	required	filtered value

Sample Code

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

gte

Return only the rows where the column value is greater than or equal to the specified value.

Parameter

Parameter	Type	Required	Description
column	string	required	column to be filtered
value	any	required	filtered value

Sample Code

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

lt

Return only the rows where the column value is less than the specified value.

Parameter

Parameter	Type	Required	Description
column	string	required	column to be filtered
value	any	required	filtered value

Sample Code

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

lte

Return only the rows where the column value is ≤ the specified value.

Parameter

Parameter	Type	Required	Description
column	string	required	column to be filtered
value	any	required	filtered value

Sample Code

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

like

Return only the rows where the column value matches a specific pattern (case sensitive based on rule constraints).

Parameter

Parameter	Type	Required	Description
column	string	required	column to be filtered
pattern	string	required	pattern to match

Sample Code

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

is

Return only the rows where the column value is equal to 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, with the same behavior as .eq().

Parameter

Parameter	Type	Required	Description
column	string	required	column to be filtered
value	Object	required	filtered value

Sample Code

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

in

Match only rows where column values are included in the specified array.

Parameter

Parameter	Type	Required	Description
column	string	required	column to be filtered
values	Array	required	array of filtered values

Sample Code

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

match

Match only rows where each column in the query key equals its associated value, equivalent to multiple .eq() in abbreviation format.

Parameter

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

Sample Code

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

not

Only match rows that do not meet filtering criteria.

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

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

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

Parameter

Parameter	Type	Required	Description
column	string	required	column to be filtered
operator	string	required	filter operator to be negated, following MySQL syntax
value	any	required	filter value, following MySQL syntax

Sample Code

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

or

Only match rows that satisfy at least one filter condition.

Unlike most filters, this filter is used as is, must follow MySQL syntax, and ensure they are properly escaped.

Currently unable to perform .or() filtering across multiple tables.

or() expects the original MySQL syntax as the filter name and value.

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

Parameter

Parameter	Type	Required	Description
filters	string	required	filter to be used, following MySQL syntax
options	object	required	named parameter
options.referencedTable	string	optional	Set to filter the reference table rather than the parent table

Sample Code

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

filter

Only match rows meeting filtering criteria. This is an escape hatch and should use specific filter methods whenever possible.

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

filter() expects the original MySQL syntax as the filter value.

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

Parameter

Parameter	Type	Required	Description
column	string	required	column to be filtered
operator	string	required	filter operator, following MySQL syntax
value	any	required	filter value, following MySQL syntax

Sample Code

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

// 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");