Table filter syntax

Filtered table and export methods use the same top-level filter fields.

Field	Format	Meaning
where	[[field, operator, value], ...]	Every comparison must match
orWhere	[[field, operator, value], ...]	At least one comparison must match
whereNot	[[field, value], ...]	Every field must differ from the value
whereIn	[[field, [values...]], ...]	Field must match one listed value
whereNotIn	[[field, [values...]], ...]	Field must not match any listed value
whereBetween	[[field, [from, to]], ...]	Field must be inside the inclusive range
whereNotBetween	[[field, [from, to]], ...]	Field must be outside the inclusive range

All standard filters are combined with AND. Conditions inside orWhere are grouped with parentheses and combined with OR:

all standard filters AND (orWhere[0] OR orWhere[1] OR ...)

For example:

{
  "where": [
    ["enable", "==", 1]
  ],
  "orWhere": [
    ["name", "like", "john"],
    ["email", "like", "john"]
  ]
}

is evaluated as:

enable = 1 AND (name LIKE "john" OR email LIKE "john")

When orWhere is present without other filters, at least one of its conditions must match. An empty or omitted orWhere does not affect the result.

Supported fields and operators depend on the method. orWhere uses the same fields, value types, and comparison operators as where for that method. Access-control constraints are applied independently and cannot be bypassed with orWhere.

Operators

Operator	Text fields	Integer fields	Floating-point fields
= / ==	Case-insensitive equality	Equality	Equality
!=	Case-insensitive inequality	Inequality	Inequality
> / < / >= / <=	Not supported	Numeric comparison	Numeric comparison
like	Case-insensitive substring match	Substring match on the decimal digits	Not supported

like on text fields

The value is matched as a case-insensitive substring. % characters are optional and are removed before matching, so "john", "%john%" and "john%" behave identically: each matches john, Johnny and Big John.

like on integer fields

The decimal representation of the value is searched for as a substring of the decimal representation of the field. It is a containment match, the same as for text fields, not a prefix match:

{
  "where": [
    ["customer_id", "like", 13]
  ]
}

matches customer_id 13, 131, 135, 13330, and also 113, 2013 and 5130.

• The value must be sent as a JSON number. A string such as "13" is not converted: the field's type check rejects it and the condition matches no record, without an error.
• Negative numbers are compared by their full representation including the sign: -130 matches 13.
• A short value matches a large share of records. Searching by a single digit matches more than half of a sequential ID range, so clients should require at least two or three digits and debounce input before sending the request.
• Bit-mask fields such as lifecycle_flags do not support like or any other scalar comparison; use their dedicated mask operators.

Integer like is evaluated by in-memory lists only. Database-backed lists reject like on a numeric field with Unsupported filter operator: like.

Apart from that difference, the same semantics are used for in-memory lists, database-backed lists, and exports.