ObjectStackFilter schema — Data Protocol reference
Unified Query DSL Specification. Reference for ComparisonOperator, EqualityOperator, FieldOperators and 9 more: every property with its type and default.
Unified Query DSL Specification
Based on industry best practices from:
- Prisma ORM
- Strapi CMS
- TypeORM
- LoopBack Framework
Version: 1.0.0 Status: Draft
Objective: Define a JSON-based, database-agnostic query syntax standard for data filtering interactions between frontend and backend APIs.
Design Principles:
- Declarative: Frontend describes "what data to get", not "how to query"
- Database Agnostic: Syntax contains no database-specific directives
- Type Safe: Structure can be statically inferred by TypeScript
- Convention over Configuration: Implicit syntax for common queries
Source: packages/spec/src/data/filter.zod.ts
TypeScript Usage
import { ComparisonOperatorSchema, EqualityOperatorSchema, FieldOperatorsSchema, FieldReferenceSchema, FilterArraySchema, FilterConditionSchema, NormalizedFilterSchema, QueryFilterSchema, RangeOperatorSchema, SetOperatorSchema, SpecialOperatorSchema, StringOperatorSchema } from '@objectstack/spec/data';
import type { FieldOperators, FieldReference, FilterArray, FilterCondition, NormalizedFilter, QueryFilter } from '@objectstack/spec/data';
// Validate data
const result = ComparisonOperatorSchema.parse(data);ComparisonOperator
Properties
| Property | Type | Required | Description |
|---|---|---|---|
| $gt | number | string | { $field: string; addDays?: integer | object } | optional | Greater than. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $gte | number | string | { $field: string; addDays?: integer | object } | optional | Greater than or equal to. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $lt | number | string | { $field: string; addDays?: integer | object } | optional | Less than. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $lte | number | string | { $field: string; addDays?: integer | object } | optional | Less than or equal to. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
Nested Shape: ComparisonOperator.$gt
| Property | Type | Required | Description |
|---|---|---|---|
| $field | string | ✅ | Field Reference/Column Name |
| addDays | integer | { $field: string } | optional | Whole-day offset added to the referenced column before comparing: an integer literal of any sign (negative subtracts; whole days only), or a { $field } reference to a numeric column. A NULL offset column contributes zero days; a NULL referenced column makes the comparison false rather than NULL, so it stays false under $not. Compiles on SQL push-down between two temporal columns of the same class (date/date, datetime/datetime) and evaluates identically in memory. |
Nested Shape: ComparisonOperator.$gte
| Property | Type | Required | Description |
|---|---|---|---|
| $field | string | ✅ | Field Reference/Column Name |
| addDays | integer | { $field: string } | optional | Whole-day offset added to the referenced column before comparing: an integer literal of any sign (negative subtracts; whole days only), or a { $field } reference to a numeric column. A NULL offset column contributes zero days; a NULL referenced column makes the comparison false rather than NULL, so it stays false under $not. Compiles on SQL push-down between two temporal columns of the same class (date/date, datetime/datetime) and evaluates identically in memory. |
Nested Shape: ComparisonOperator.$lt
| Property | Type | Required | Description |
|---|---|---|---|
| $field | string | ✅ | Field Reference/Column Name |
| addDays | integer | { $field: string } | optional | Whole-day offset added to the referenced column before comparing: an integer literal of any sign (negative subtracts; whole days only), or a { $field } reference to a numeric column. A NULL offset column contributes zero days; a NULL referenced column makes the comparison false rather than NULL, so it stays false under $not. Compiles on SQL push-down between two temporal columns of the same class (date/date, datetime/datetime) and evaluates identically in memory. |
Nested Shape: ComparisonOperator.$lte
| Property | Type | Required | Description |
|---|---|---|---|
| $field | string | ✅ | Field Reference/Column Name |
| addDays | integer | { $field: string } | optional | Whole-day offset added to the referenced column before comparing: an integer literal of any sign (negative subtracts; whole days only), or a { $field } reference to a numeric column. A NULL offset column contributes zero days; a NULL referenced column makes the comparison false rather than NULL, so it stays false under $not. Compiles on SQL push-down between two temporal columns of the same class (date/date, datetime/datetime) and evaluates identically in memory. |
EqualityOperator
Properties
| Property | Type | Required | Description |
|---|---|---|---|
| $eq | any | optional | Equal to — the DEFAULT operator: a bare value written against a field key is the same condition as this one. Lowered to = on the SQL family and to $eq on MongoDB. The comparand is a literal, or a { $field } reference to another column of the same table ($eq is one of the six scalar comparisons a reference may be the whole comparand of). { "$eq": null } is the has-NO-value predicate — the same question { "$null": true } asks, and it is about the VALUE, never about whether a key is present. |
| $ne | any | optional | Not equal to. Lowered to <> / != on the SQL family and to $ne on MongoDB. The comparand is a literal, or a { $field } reference to another column of the same table ($ne is one of the six scalar comparisons a reference may be the whole comparand of). { "$ne": null } is the HAS-A-VALUE predicate — the same question { "$exists": true } asks: it matches rows whose field holds a value, never rows that merely carry the key. |
FieldOperators
Properties
| Property | Type | Required | Description |
|---|---|---|---|
| $eq | any | optional | Equal to — the DEFAULT operator: a bare value written against a field key is the same condition as this one. Lowered to = on the SQL family and to $eq on MongoDB. The comparand is a literal, or a { $field } reference to another column of the same table ($eq is one of the six scalar comparisons a reference may be the whole comparand of). { "$eq": null } is the has-NO-value predicate — the same question { "$null": true } asks, and it is about the VALUE, never about whether a key is present. |
| $ne | any | optional | Not equal to. Lowered to <> / != on the SQL family and to $ne on MongoDB. The comparand is a literal, or a { $field } reference to another column of the same table ($ne is one of the six scalar comparisons a reference may be the whole comparand of). { "$ne": null } is the HAS-A-VALUE predicate — the same question { "$exists": true } asks: it matches rows whose field holds a value, never rows that merely carry the key. |
| $gt | number | string | { $field: string; addDays?: integer | object } | optional | Greater than. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $gte | number | string | { $field: string; addDays?: integer | object } | optional | Greater than or equal to. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $lt | number | string | { $field: string; addDays?: integer | object } | optional | Less than. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $lte | number | string | { $field: string; addDays?: integer | object } | optional | Less than or equal to. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $in | any[] | optional | Membership list. Members are literal values of any type the column stores. A { $field } reference is NOT a member shape: no backend resolves one inside a list — put it in a scalar comparison ($eq/$ne/$gt/$gte/$lt/$lte) instead. null is NOT a member shape either: state absence explicitly with the null predicate — "one of […] OR has no value" is { "$or": [{ "$in": […] }, { "$null": true }] }. |
| $nin | any[] | optional | Membership list. Members are literal values of any type the column stores. A { $field } reference is NOT a member shape: no backend resolves one inside a list — put it in a scalar comparison ($eq/$ne/$gt/$gte/$lt/$lte) instead. null is NOT a member shape either: state absence explicitly with the null predicate — "one of […] OR has no value" is { "$or": [{ "$in": […] }, { "$null": true }] }. |
| $between | [number | string, number | string] | optional | Between (inclusive). Closed interval [min, max]. Each endpoint is a number, a Date, or a string, and BOTH are required NON-BLANK: an empty string, null and undefined are refused, and the refusal names the blank side. A range bounded on ONE side only is not a $between at all — write the side you have as a scalar comparison ($gte for a lower bound, $lte for an upper one). A { $field } reference is NOT an endpoint shape: no backend resolves one inside a list — put it in a scalar comparison ($gt/$gte/$lt/$lte), which does compile to a column-to-column bound. STRING is the form the platform itself produces: the date-macro resolver walks INTO arrays, so { $between: ["{current_year_start}", "{current_year_end}"] } resolves to two strings. The guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column; those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees. The driver reconciles each endpoint with the column independently (a bare calendar day used as the MAX becomes the half-open next-day boundary). Ranging over NON-temporal text is permitted but NOT promised: the order is the backend collation's, and those coincide only for ASCII. |
| $contains | string | optional | Contains substring, CASE-SENSITIVELY — "acme" does NOT match "ACME". Lowered to LIKE '%?%' (case-exact) on the SQL family, and answered case-exactly on every JS evaluation face the platform ships. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). Case-INSENSITIVE containment is $icontains, which folds ASCII case only. |
| $notContains | string | optional | Does not contain substring, CASE-SENSITIVELY — the negation of $contains, on the same comparand contract. Lowered to NOT LIKE '%?%' (case-exact) on the SQL family. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). |
| $startsWith | string | optional | Starts with prefix, CASE-SENSITIVELY. Lowered to LIKE '?%' (case-exact) on the SQL family. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). |
| $endsWith | string | optional | Ends with suffix, CASE-SENSITIVELY. Lowered to LIKE '%?' (case-exact) on the SQL family. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). |
| $icontains | string | optional | Contains substring, ignoring case — but ONLY ASCII case (A-Z against a-z). Every other character compares literally, so "café" does NOT match "CAFÉ" and "москва" does not match "МОСКВА". The domain is ASCII because that is the one fold all five backends can deliver: SQLite (and therefore turso and sqlite-wasm) folds ASCII only, so a Unicode promise here would be a guarantee three of the five could not keep. The comparand is matched LITERALLY — "%", "_" and regex metacharacters are ordinary characters, not wildcards. Case-SENSITIVE containment is $contains. Lowered on the SQL family (driver-sql, driver-sqlite-wasm, driver-turso on both transports) and on every JS evaluation face, so it is portable across every backend the platform ships. |
| $like | string | optional | Whole-string pattern match with CALLER-bound wildcards: "%" matches any sequence (including empty), "" matches exactly one character, and a character is one Unicode code point — an emoji or any other character outside the Basic Multilingual Plane is ONE "", never two, as SQL LIKE counts it. A backslash escapes the character after it ("\%", "\_", "\\") so it matches literally. The pattern must cover the WHOLE value — a pattern with no wildcards is an exact comparison, NOT a substring search; write $contains for containment. A pattern ending in a lone unpaired backslash, or holding the NUL character U+0000, is refused (INVALID_FILTER). Comparison is case-SENSITIVE, same contract as $contains (Q2 = A); $ilike is the case-insensitive twin. Answered by the SQL family (driver-sql, driver-sqlite-wasm, driver-turso on both transports), by driver-memory and by @objectstack/formula. driver-mongodb, objectql having and service-analytics REFUSE it in the INVALID_FILTER envelope rather than approximating it — see FILTER_OPERATORS for why it is staged out of that allowlist. |
| $ilike | string | optional | Whole-string pattern match like $like — "%" / "_" wildcards bound by the caller, backslash escapes — but ignoring ASCII case (A-Z against a-z) and ONLY ASCII case: "café" does NOT match "CAFÉ", the same Q1 = A boundary $icontains declares, because SQLite's fold is ASCII-only and three of the five backends are SQLite underneath. Staged with $like — see FILTER_OPERATORS. |
| $null | boolean | optional | Is-null check. true matches rows where the field is null, false matches rows where it is not null. Lowered to IS NULL (true) / IS NOT NULL (false) on the SQL family and to { field: null } (true) / { $ne: null } (false) on MongoDB. |
| $exists | boolean | optional | Has-a-value check — the exact inverse of $null. true matches rows where the field holds a value (!= null), false matches rows where it holds none. Portable across every backend the platform ships: lowered to IS NOT NULL (true) / IS NULL (false) on the SQL family and to { $ne: null } (true) / { $eq: null } (false) on MongoDB. |
| $empty | boolean | optional | Is-empty check by the field's DECLARED type. true matches rows whose field is empty, false is its exact complement. What counts as empty: text-like types (text, textarea, email, url, phone, password, secret, markdown, html, richtext, code, color, signature, qrcode) = null or '' (the empty string); multi-value types (multiselect, checkboxes, tags, and select, radio, lookup, user, file or image with multiple: true) = null or [] (the empty list); every other type = null only. A face that holds no field declaration judges by the value: null, '' and [] are empty. A face that answers by the declared type refuses the operator on a column whose declaration it does not hold (the built-in id, for one) rather than guess a row; use $null there for "has no value". The view operators is_empty / is_not_empty lower to this operator. |
Nested Shape: FieldOperators.$gt
| Property | Type | Required | Description |
|---|---|---|---|
| $field | string | ✅ | Field Reference/Column Name |
| addDays | integer | { $field: string } | optional | Whole-day offset added to the referenced column before comparing: an integer literal of any sign (negative subtracts; whole days only), or a { $field } reference to a numeric column. A NULL offset column contributes zero days; a NULL referenced column makes the comparison false rather than NULL, so it stays false under $not. Compiles on SQL push-down between two temporal columns of the same class (date/date, datetime/datetime) and evaluates identically in memory. |
Nested Shape: FieldOperators.$gte
| Property | Type | Required | Description |
|---|---|---|---|
| $field | string | ✅ | Field Reference/Column Name |
| addDays | integer | { $field: string } | optional | Whole-day offset added to the referenced column before comparing: an integer literal of any sign (negative subtracts; whole days only), or a { $field } reference to a numeric column. A NULL offset column contributes zero days; a NULL referenced column makes the comparison false rather than NULL, so it stays false under $not. Compiles on SQL push-down between two temporal columns of the same class (date/date, datetime/datetime) and evaluates identically in memory. |
Nested Shape: FieldOperators.$lt
| Property | Type | Required | Description |
|---|---|---|---|
| $field | string | ✅ | Field Reference/Column Name |
| addDays | integer | { $field: string } | optional | Whole-day offset added to the referenced column before comparing: an integer literal of any sign (negative subtracts; whole days only), or a { $field } reference to a numeric column. A NULL offset column contributes zero days; a NULL referenced column makes the comparison false rather than NULL, so it stays false under $not. Compiles on SQL push-down between two temporal columns of the same class (date/date, datetime/datetime) and evaluates identically in memory. |
Nested Shape: FieldOperators.$lte
| Property | Type | Required | Description |
|---|---|---|---|
| $field | string | ✅ | Field Reference/Column Name |
| addDays | integer | { $field: string } | optional | Whole-day offset added to the referenced column before comparing: an integer literal of any sign (negative subtracts; whole days only), or a { $field } reference to a numeric column. A NULL offset column contributes zero days; a NULL referenced column makes the comparison false rather than NULL, so it stays false under $not. Compiles on SQL push-down between two temporal columns of the same class (date/date, datetime/datetime) and evaluates identically in memory. |
FieldReference
Properties
| Property | Type | Required | Description |
|---|---|---|---|
| $field | string | ✅ | Field Reference/Column Name |
| addDays | integer | { $field: string } | optional | Whole-day offset added to the referenced column before comparing: an integer literal of any sign (negative subtracts; whole days only), or a { $field } reference to a numeric column. A NULL offset column contributes zero days; a NULL referenced column makes the comparison false rather than NULL, so it stays false under $not. Compiles on SQL push-down between two temporal columns of the same class (date/date, datetime/datetime) and evaluates identically in memory. |
Nested Shape: FieldReference.addDays
A { $field } reference to the numeric column holding the day offset
| Property | Type | Required | Description |
|---|---|---|---|
| $field | string | ✅ | Numeric column whose value is the number of days to add |
FilterArray
Input-only authoring sugar for a filter: [field, operator, value], ["and"|"or", ...conditions], or a bare list of those. Lowered to a FilterCondition at the single sink parseFilterAST (@objectstack/spec/data) the moment it arrives; it is never stored and never travels the wire as an array. A query "where" is a FilterCondition and does not accept this shape.
Union Options
This schema accepts one of the following structures:
Option 1
Type: [string, string, any]
Option 2
Type: [string, string]
Option 3
Type: [string, [FilterArray](#filterarray), ...[FilterArray](#filterarray)[]]
Option 4
Type: [FilterArray](#filterarray)[]
FilterCondition
Type: Record<string, any> & { $and?: [FilterCondition](#filtercondition)[]; $or?: [FilterCondition](#filtercondition)[]; $not?: [FilterCondition](#filtercondition) }
NormalizedFilter
Properties
| Property | Type | Required | Description |
|---|---|---|---|
| $and | (Record<string, { $eq?: any; $ne?: any; $gt?: number | string | object; $gte?: number | string | object; … }> | [NormalizedFilter](#normalizedfilter))[] | optional | |
| $or | (Record<string, { $eq?: any; $ne?: any; $gt?: number | string | object; $gte?: number | string | object; … }> | [NormalizedFilter](#normalizedfilter))[] | optional | |
| $not | Record<string, { $eq?: any; $ne?: any; $gt?: number | string | object; $gte?: number | string | object; … }> | [NormalizedFilter](#normalizedfilter) | optional |
Nested Shape: NormalizedFilter.$and[number][string]
| Property | Type | Required | Description |
|---|---|---|---|
| $eq | any | optional | Equal to — the DEFAULT operator: a bare value written against a field key is the same condition as this one. Lowered to = on the SQL family and to $eq on MongoDB. The comparand is a literal, or a { $field } reference to another column of the same table ($eq is one of the six scalar comparisons a reference may be the whole comparand of). { "$eq": null } is the has-NO-value predicate — the same question { "$null": true } asks, and it is about the VALUE, never about whether a key is present. |
| $ne | any | optional | Not equal to. Lowered to <> / != on the SQL family and to $ne on MongoDB. The comparand is a literal, or a { $field } reference to another column of the same table ($ne is one of the six scalar comparisons a reference may be the whole comparand of). { "$ne": null } is the HAS-A-VALUE predicate — the same question { "$exists": true } asks: it matches rows whose field holds a value, never rows that merely carry the key. |
| $gt | number | string | { $field: string; addDays?: integer | object } | optional | Greater than. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $gte | number | string | { $field: string; addDays?: integer | object } | optional | Greater than or equal to. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $lt | number | string | { $field: string; addDays?: integer | object } | optional | Less than. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $lte | number | string | { $field: string; addDays?: integer | object } | optional | Less than or equal to. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $in | any[] | optional | Membership list. Members are literal values of any type the column stores. A { $field } reference is NOT a member shape: no backend resolves one inside a list — put it in a scalar comparison ($eq/$ne/$gt/$gte/$lt/$lte) instead. null is NOT a member shape either: state absence explicitly with the null predicate — "one of […] OR has no value" is { "$or": [{ "$in": […] }, { "$null": true }] }. |
| $nin | any[] | optional | Membership list. Members are literal values of any type the column stores. A { $field } reference is NOT a member shape: no backend resolves one inside a list — put it in a scalar comparison ($eq/$ne/$gt/$gte/$lt/$lte) instead. null is NOT a member shape either: state absence explicitly with the null predicate — "one of […] OR has no value" is { "$or": [{ "$in": […] }, { "$null": true }] }. |
| $between | [number | string, number | string] | optional | Between (inclusive). Closed interval [min, max]. Each endpoint is a number, a Date, or a string, and BOTH are required NON-BLANK: an empty string, null and undefined are refused, and the refusal names the blank side. A range bounded on ONE side only is not a $between at all — write the side you have as a scalar comparison ($gte for a lower bound, $lte for an upper one). A { $field } reference is NOT an endpoint shape: no backend resolves one inside a list — put it in a scalar comparison ($gt/$gte/$lt/$lte), which does compile to a column-to-column bound. STRING is the form the platform itself produces: the date-macro resolver walks INTO arrays, so { $between: ["{current_year_start}", "{current_year_end}"] } resolves to two strings. The guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column; those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees. The driver reconciles each endpoint with the column independently (a bare calendar day used as the MAX becomes the half-open next-day boundary). Ranging over NON-temporal text is permitted but NOT promised: the order is the backend collation's, and those coincide only for ASCII. |
| $contains | string | optional | Contains substring, CASE-SENSITIVELY — "acme" does NOT match "ACME". Lowered to LIKE '%?%' (case-exact) on the SQL family, and answered case-exactly on every JS evaluation face the platform ships. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). Case-INSENSITIVE containment is $icontains, which folds ASCII case only. |
| $notContains | string | optional | Does not contain substring, CASE-SENSITIVELY — the negation of $contains, on the same comparand contract. Lowered to NOT LIKE '%?%' (case-exact) on the SQL family. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). |
| $startsWith | string | optional | Starts with prefix, CASE-SENSITIVELY. Lowered to LIKE '?%' (case-exact) on the SQL family. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). |
| $endsWith | string | optional | Ends with suffix, CASE-SENSITIVELY. Lowered to LIKE '%?' (case-exact) on the SQL family. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). |
| $icontains | string | optional | Contains substring, ignoring case — but ONLY ASCII case (A-Z against a-z). Every other character compares literally, so "café" does NOT match "CAFÉ" and "москва" does not match "МОСКВА". The domain is ASCII because that is the one fold all five backends can deliver: SQLite (and therefore turso and sqlite-wasm) folds ASCII only, so a Unicode promise here would be a guarantee three of the five could not keep. The comparand is matched LITERALLY — "%", "_" and regex metacharacters are ordinary characters, not wildcards. Case-SENSITIVE containment is $contains. Lowered on the SQL family (driver-sql, driver-sqlite-wasm, driver-turso on both transports) and on every JS evaluation face, so it is portable across every backend the platform ships. |
| $like | string | optional | Whole-string pattern match with CALLER-bound wildcards: "%" matches any sequence (including empty), "" matches exactly one character, and a character is one Unicode code point — an emoji or any other character outside the Basic Multilingual Plane is ONE "", never two, as SQL LIKE counts it. A backslash escapes the character after it ("\%", "\_", "\\") so it matches literally. The pattern must cover the WHOLE value — a pattern with no wildcards is an exact comparison, NOT a substring search; write $contains for containment. A pattern ending in a lone unpaired backslash, or holding the NUL character U+0000, is refused (INVALID_FILTER). Comparison is case-SENSITIVE, same contract as $contains (Q2 = A); $ilike is the case-insensitive twin. Answered by the SQL family (driver-sql, driver-sqlite-wasm, driver-turso on both transports), by driver-memory and by @objectstack/formula. driver-mongodb, objectql having and service-analytics REFUSE it in the INVALID_FILTER envelope rather than approximating it — see FILTER_OPERATORS for why it is staged out of that allowlist. |
| $ilike | string | optional | Whole-string pattern match like $like — "%" / "_" wildcards bound by the caller, backslash escapes — but ignoring ASCII case (A-Z against a-z) and ONLY ASCII case: "café" does NOT match "CAFÉ", the same Q1 = A boundary $icontains declares, because SQLite's fold is ASCII-only and three of the five backends are SQLite underneath. Staged with $like — see FILTER_OPERATORS. |
| $null | boolean | optional | Is-null check. true matches rows where the field is null, false matches rows where it is not null. Lowered to IS NULL (true) / IS NOT NULL (false) on the SQL family and to { field: null } (true) / { $ne: null } (false) on MongoDB. |
| $exists | boolean | optional | Has-a-value check — the exact inverse of $null. true matches rows where the field holds a value (!= null), false matches rows where it holds none. Portable across every backend the platform ships: lowered to IS NOT NULL (true) / IS NULL (false) on the SQL family and to { $ne: null } (true) / { $eq: null } (false) on MongoDB. |
| $empty | boolean | optional | Is-empty check by the field's DECLARED type. true matches rows whose field is empty, false is its exact complement. What counts as empty: text-like types (text, textarea, email, url, phone, password, secret, markdown, html, richtext, code, color, signature, qrcode) = null or '' (the empty string); multi-value types (multiselect, checkboxes, tags, and select, radio, lookup, user, file or image with multiple: true) = null or [] (the empty list); every other type = null only. A face that holds no field declaration judges by the value: null, '' and [] are empty. A face that answers by the declared type refuses the operator on a column whose declaration it does not hold (the built-in id, for one) rather than guess a row; use $null there for "has no value". The view operators is_empty / is_not_empty lower to this operator. |
Nested Shape: NormalizedFilter.$or[number][string]
| Property | Type | Required | Description |
|---|---|---|---|
| $eq | any | optional | Equal to — the DEFAULT operator: a bare value written against a field key is the same condition as this one. Lowered to = on the SQL family and to $eq on MongoDB. The comparand is a literal, or a { $field } reference to another column of the same table ($eq is one of the six scalar comparisons a reference may be the whole comparand of). { "$eq": null } is the has-NO-value predicate — the same question { "$null": true } asks, and it is about the VALUE, never about whether a key is present. |
| $ne | any | optional | Not equal to. Lowered to <> / != on the SQL family and to $ne on MongoDB. The comparand is a literal, or a { $field } reference to another column of the same table ($ne is one of the six scalar comparisons a reference may be the whole comparand of). { "$ne": null } is the HAS-A-VALUE predicate — the same question { "$exists": true } asks: it matches rows whose field holds a value, never rows that merely carry the key. |
| $gt | number | string | { $field: string; addDays?: integer | object } | optional | Greater than. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $gte | number | string | { $field: string; addDays?: integer | object } | optional | Greater than or equal to. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $lt | number | string | { $field: string; addDays?: integer | object } | optional | Less than. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $lte | number | string | { $field: string; addDays?: integer | object } | optional | Less than or equal to. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $in | any[] | optional | Membership list. Members are literal values of any type the column stores. A { $field } reference is NOT a member shape: no backend resolves one inside a list — put it in a scalar comparison ($eq/$ne/$gt/$gte/$lt/$lte) instead. null is NOT a member shape either: state absence explicitly with the null predicate — "one of […] OR has no value" is { "$or": [{ "$in": […] }, { "$null": true }] }. |
| $nin | any[] | optional | Membership list. Members are literal values of any type the column stores. A { $field } reference is NOT a member shape: no backend resolves one inside a list — put it in a scalar comparison ($eq/$ne/$gt/$gte/$lt/$lte) instead. null is NOT a member shape either: state absence explicitly with the null predicate — "one of […] OR has no value" is { "$or": [{ "$in": […] }, { "$null": true }] }. |
| $between | [number | string, number | string] | optional | Between (inclusive). Closed interval [min, max]. Each endpoint is a number, a Date, or a string, and BOTH are required NON-BLANK: an empty string, null and undefined are refused, and the refusal names the blank side. A range bounded on ONE side only is not a $between at all — write the side you have as a scalar comparison ($gte for a lower bound, $lte for an upper one). A { $field } reference is NOT an endpoint shape: no backend resolves one inside a list — put it in a scalar comparison ($gt/$gte/$lt/$lte), which does compile to a column-to-column bound. STRING is the form the platform itself produces: the date-macro resolver walks INTO arrays, so { $between: ["{current_year_start}", "{current_year_end}"] } resolves to two strings. The guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column; those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees. The driver reconciles each endpoint with the column independently (a bare calendar day used as the MAX becomes the half-open next-day boundary). Ranging over NON-temporal text is permitted but NOT promised: the order is the backend collation's, and those coincide only for ASCII. |
| $contains | string | optional | Contains substring, CASE-SENSITIVELY — "acme" does NOT match "ACME". Lowered to LIKE '%?%' (case-exact) on the SQL family, and answered case-exactly on every JS evaluation face the platform ships. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). Case-INSENSITIVE containment is $icontains, which folds ASCII case only. |
| $notContains | string | optional | Does not contain substring, CASE-SENSITIVELY — the negation of $contains, on the same comparand contract. Lowered to NOT LIKE '%?%' (case-exact) on the SQL family. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). |
| $startsWith | string | optional | Starts with prefix, CASE-SENSITIVELY. Lowered to LIKE '?%' (case-exact) on the SQL family. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). |
| $endsWith | string | optional | Ends with suffix, CASE-SENSITIVELY. Lowered to LIKE '%?' (case-exact) on the SQL family. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). |
| $icontains | string | optional | Contains substring, ignoring case — but ONLY ASCII case (A-Z against a-z). Every other character compares literally, so "café" does NOT match "CAFÉ" and "москва" does not match "МОСКВА". The domain is ASCII because that is the one fold all five backends can deliver: SQLite (and therefore turso and sqlite-wasm) folds ASCII only, so a Unicode promise here would be a guarantee three of the five could not keep. The comparand is matched LITERALLY — "%", "_" and regex metacharacters are ordinary characters, not wildcards. Case-SENSITIVE containment is $contains. Lowered on the SQL family (driver-sql, driver-sqlite-wasm, driver-turso on both transports) and on every JS evaluation face, so it is portable across every backend the platform ships. |
| $like | string | optional | Whole-string pattern match with CALLER-bound wildcards: "%" matches any sequence (including empty), "" matches exactly one character, and a character is one Unicode code point — an emoji or any other character outside the Basic Multilingual Plane is ONE "", never two, as SQL LIKE counts it. A backslash escapes the character after it ("\%", "\_", "\\") so it matches literally. The pattern must cover the WHOLE value — a pattern with no wildcards is an exact comparison, NOT a substring search; write $contains for containment. A pattern ending in a lone unpaired backslash, or holding the NUL character U+0000, is refused (INVALID_FILTER). Comparison is case-SENSITIVE, same contract as $contains (Q2 = A); $ilike is the case-insensitive twin. Answered by the SQL family (driver-sql, driver-sqlite-wasm, driver-turso on both transports), by driver-memory and by @objectstack/formula. driver-mongodb, objectql having and service-analytics REFUSE it in the INVALID_FILTER envelope rather than approximating it — see FILTER_OPERATORS for why it is staged out of that allowlist. |
| $ilike | string | optional | Whole-string pattern match like $like — "%" / "_" wildcards bound by the caller, backslash escapes — but ignoring ASCII case (A-Z against a-z) and ONLY ASCII case: "café" does NOT match "CAFÉ", the same Q1 = A boundary $icontains declares, because SQLite's fold is ASCII-only and three of the five backends are SQLite underneath. Staged with $like — see FILTER_OPERATORS. |
| $null | boolean | optional | Is-null check. true matches rows where the field is null, false matches rows where it is not null. Lowered to IS NULL (true) / IS NOT NULL (false) on the SQL family and to { field: null } (true) / { $ne: null } (false) on MongoDB. |
| $exists | boolean | optional | Has-a-value check — the exact inverse of $null. true matches rows where the field holds a value (!= null), false matches rows where it holds none. Portable across every backend the platform ships: lowered to IS NOT NULL (true) / IS NULL (false) on the SQL family and to { $ne: null } (true) / { $eq: null } (false) on MongoDB. |
| $empty | boolean | optional | Is-empty check by the field's DECLARED type. true matches rows whose field is empty, false is its exact complement. What counts as empty: text-like types (text, textarea, email, url, phone, password, secret, markdown, html, richtext, code, color, signature, qrcode) = null or '' (the empty string); multi-value types (multiselect, checkboxes, tags, and select, radio, lookup, user, file or image with multiple: true) = null or [] (the empty list); every other type = null only. A face that holds no field declaration judges by the value: null, '' and [] are empty. A face that answers by the declared type refuses the operator on a column whose declaration it does not hold (the built-in id, for one) rather than guess a row; use $null there for "has no value". The view operators is_empty / is_not_empty lower to this operator. |
Nested Shape: NormalizedFilter.$not[string]
| Property | Type | Required | Description |
|---|---|---|---|
| $eq | any | optional | Equal to — the DEFAULT operator: a bare value written against a field key is the same condition as this one. Lowered to = on the SQL family and to $eq on MongoDB. The comparand is a literal, or a { $field } reference to another column of the same table ($eq is one of the six scalar comparisons a reference may be the whole comparand of). { "$eq": null } is the has-NO-value predicate — the same question { "$null": true } asks, and it is about the VALUE, never about whether a key is present. |
| $ne | any | optional | Not equal to. Lowered to <> / != on the SQL family and to $ne on MongoDB. The comparand is a literal, or a { $field } reference to another column of the same table ($ne is one of the six scalar comparisons a reference may be the whole comparand of). { "$ne": null } is the HAS-A-VALUE predicate — the same question { "$exists": true } asks: it matches rows whose field holds a value, never rows that merely carry the key. |
| $gt | number | string | { $field: string; addDays?: integer | object } | optional | Greater than. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $gte | number | string | { $field: string; addDays?: integer | object } | optional | Greater than or equal to. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $lt | number | string | { $field: string; addDays?: integer | object } | optional | Less than. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $lte | number | string | { $field: string; addDays?: integer | object } | optional | Less than or equal to. Comparand is a number, a Date, a string, or a { $field } reference (optionally carrying a whole-day addDays offset). STRING is the form the platform itself produces: the date-macro resolver returns only strings ("{current_year_start}" -> "2026-01-01"), and the guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column. Those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees; the driver reconciles the comparand with the column (a bare calendar day used as an upper bound becomes the half-open next-day boundary). Ordering NON-temporal text is permitted but NOT promised: the order is the backend collation's (byte-wise on SQLite, the database locale on Postgres, UTF-16 code units in the JS matchers), and those coincide only for ASCII. null is NOT a comparand: null is not ordered — state absence with the null predicate instead ($eq: null is "has no value", $ne: null is "has a value"). |
| $in | any[] | optional | Membership list. Members are literal values of any type the column stores. A { $field } reference is NOT a member shape: no backend resolves one inside a list — put it in a scalar comparison ($eq/$ne/$gt/$gte/$lt/$lte) instead. null is NOT a member shape either: state absence explicitly with the null predicate — "one of […] OR has no value" is { "$or": [{ "$in": […] }, { "$null": true }] }. |
| $nin | any[] | optional | Membership list. Members are literal values of any type the column stores. A { $field } reference is NOT a member shape: no backend resolves one inside a list — put it in a scalar comparison ($eq/$ne/$gt/$gte/$lt/$lte) instead. null is NOT a member shape either: state absence explicitly with the null predicate — "one of […] OR has no value" is { "$or": [{ "$in": […] }, { "$null": true }] }. |
| $between | [number | string, number | string] | optional | Between (inclusive). Closed interval [min, max]. Each endpoint is a number, a Date, or a string, and BOTH are required NON-BLANK: an empty string, null and undefined are refused, and the refusal names the blank side. A range bounded on ONE side only is not a $between at all — write the side you have as a scalar comparison ($gte for a lower bound, $lte for an upper one). A { $field } reference is NOT an endpoint shape: no backend resolves one inside a list — put it in a scalar comparison ($gt/$gte/$lt/$lte), which does compile to a column-to-column bound. STRING is the form the platform itself produces: the date-macro resolver walks INTO arrays, so { $between: ["{current_year_start}", "{current_year_end}"] } resolves to two strings. The guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column; those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees. The driver reconciles each endpoint with the column independently (a bare calendar day used as the MAX becomes the half-open next-day boundary). Ranging over NON-temporal text is permitted but NOT promised: the order is the backend collation's, and those coincide only for ASCII. |
| $contains | string | optional | Contains substring, CASE-SENSITIVELY — "acme" does NOT match "ACME". Lowered to LIKE '%?%' (case-exact) on the SQL family, and answered case-exactly on every JS evaluation face the platform ships. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). Case-INSENSITIVE containment is $icontains, which folds ASCII case only. |
| $notContains | string | optional | Does not contain substring, CASE-SENSITIVELY — the negation of $contains, on the same comparand contract. Lowered to NOT LIKE '%?%' (case-exact) on the SQL family. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). |
| $startsWith | string | optional | Starts with prefix, CASE-SENSITIVELY. Lowered to LIKE '?%' (case-exact) on the SQL family. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). |
| $endsWith | string | optional | Ends with suffix, CASE-SENSITIVELY. Lowered to LIKE '%?' (case-exact) on the SQL family. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). |
| $icontains | string | optional | Contains substring, ignoring case — but ONLY ASCII case (A-Z against a-z). Every other character compares literally, so "café" does NOT match "CAFÉ" and "москва" does not match "МОСКВА". The domain is ASCII because that is the one fold all five backends can deliver: SQLite (and therefore turso and sqlite-wasm) folds ASCII only, so a Unicode promise here would be a guarantee three of the five could not keep. The comparand is matched LITERALLY — "%", "_" and regex metacharacters are ordinary characters, not wildcards. Case-SENSITIVE containment is $contains. Lowered on the SQL family (driver-sql, driver-sqlite-wasm, driver-turso on both transports) and on every JS evaluation face, so it is portable across every backend the platform ships. |
| $like | string | optional | Whole-string pattern match with CALLER-bound wildcards: "%" matches any sequence (including empty), "" matches exactly one character, and a character is one Unicode code point — an emoji or any other character outside the Basic Multilingual Plane is ONE "", never two, as SQL LIKE counts it. A backslash escapes the character after it ("\%", "\_", "\\") so it matches literally. The pattern must cover the WHOLE value — a pattern with no wildcards is an exact comparison, NOT a substring search; write $contains for containment. A pattern ending in a lone unpaired backslash, or holding the NUL character U+0000, is refused (INVALID_FILTER). Comparison is case-SENSITIVE, same contract as $contains (Q2 = A); $ilike is the case-insensitive twin. Answered by the SQL family (driver-sql, driver-sqlite-wasm, driver-turso on both transports), by driver-memory and by @objectstack/formula. driver-mongodb, objectql having and service-analytics REFUSE it in the INVALID_FILTER envelope rather than approximating it — see FILTER_OPERATORS for why it is staged out of that allowlist. |
| $ilike | string | optional | Whole-string pattern match like $like — "%" / "_" wildcards bound by the caller, backslash escapes — but ignoring ASCII case (A-Z against a-z) and ONLY ASCII case: "café" does NOT match "CAFÉ", the same Q1 = A boundary $icontains declares, because SQLite's fold is ASCII-only and three of the five backends are SQLite underneath. Staged with $like — see FILTER_OPERATORS. |
| $null | boolean | optional | Is-null check. true matches rows where the field is null, false matches rows where it is not null. Lowered to IS NULL (true) / IS NOT NULL (false) on the SQL family and to { field: null } (true) / { $ne: null } (false) on MongoDB. |
| $exists | boolean | optional | Has-a-value check — the exact inverse of $null. true matches rows where the field holds a value (!= null), false matches rows where it holds none. Portable across every backend the platform ships: lowered to IS NOT NULL (true) / IS NULL (false) on the SQL family and to { $ne: null } (true) / { $eq: null } (false) on MongoDB. |
| $empty | boolean | optional | Is-empty check by the field's DECLARED type. true matches rows whose field is empty, false is its exact complement. What counts as empty: text-like types (text, textarea, email, url, phone, password, secret, markdown, html, richtext, code, color, signature, qrcode) = null or '' (the empty string); multi-value types (multiselect, checkboxes, tags, and select, radio, lookup, user, file or image with multiple: true) = null or [] (the empty list); every other type = null only. A face that holds no field declaration judges by the value: null, '' and [] are empty. A face that answers by the declared type refuses the operator on a column whose declaration it does not hold (the built-in id, for one) rather than guess a row; use $null there for "has no value". The view operators is_empty / is_not_empty lower to this operator. |
QueryFilter
Properties
| Property | Type | Required | Description |
|---|---|---|---|
| where | any | optional | The condition tree the query filters by. A field-keyed entry is a condition on that field — a bare value is implicit equality, an object is a map of field operators — and $and / $or / $not combine conditions. $not is NULL-safe: a row whose compared column is null does NOT satisfy the negated condition and IS returned. An empty $and is the AND identity (no constraint); an empty $or is the OR identity (zero rows). |
RangeOperator
Properties
| Property | Type | Required | Description |
|---|---|---|---|
| $between | [number | string, number | string] | optional | Between (inclusive). Closed interval [min, max]. Each endpoint is a number, a Date, or a string, and BOTH are required NON-BLANK: an empty string, null and undefined are refused, and the refusal names the blank side. A range bounded on ONE side only is not a $between at all — write the side you have as a scalar comparison ($gte for a lower bound, $lte for an upper one). A { $field } reference is NOT an endpoint shape: no backend resolves one inside a list — put it in a scalar comparison ($gt/$gte/$lt/$lte), which does compile to a column-to-column bound. STRING is the form the platform itself produces: the date-macro resolver walks INTO arrays, so { $between: ["{current_year_start}", "{current_year_end}"] } resolves to two strings. The guaranteed spellings are an ISO calendar day (YYYY-MM-DD), a UTC ISO-8601 instant, or a wall-clock time of day (HH:MM[:SS[.fff]]) for a Field.time column; those are ASCII and fixed-width, so lexicographic order IS chronological order and every backend agrees. The driver reconciles each endpoint with the column independently (a bare calendar day used as the MAX becomes the half-open next-day boundary). Ranging over NON-temporal text is permitted but NOT promised: the order is the backend collation's, and those coincide only for ASCII. |
SetOperator
Properties
| Property | Type | Required | Description |
|---|---|---|---|
| $in | any[] | optional | Membership list. Members are literal values of any type the column stores. A { $field } reference is NOT a member shape: no backend resolves one inside a list — put it in a scalar comparison ($eq/$ne/$gt/$gte/$lt/$lte) instead. null is NOT a member shape either: state absence explicitly with the null predicate — "one of […] OR has no value" is { "$or": [{ "$in": […] }, { "$null": true }] }. |
| $nin | any[] | optional | Membership list. Members are literal values of any type the column stores. A { $field } reference is NOT a member shape: no backend resolves one inside a list — put it in a scalar comparison ($eq/$ne/$gt/$gte/$lt/$lte) instead. null is NOT a member shape either: state absence explicitly with the null predicate — "one of […] OR has no value" is { "$or": [{ "$in": […] }, { "$null": true }] }. |
SpecialOperator
Properties
| Property | Type | Required | Description |
|---|---|---|---|
| $null | boolean | optional | Is-null check. true matches rows where the field is null, false matches rows where it is not null. Lowered to IS NULL (true) / IS NOT NULL (false) on the SQL family and to { field: null } (true) / { $ne: null } (false) on MongoDB. |
| $exists | boolean | optional | Has-a-value check — the exact inverse of $null. true matches rows where the field holds a value (!= null), false matches rows where it holds none. Portable across every backend the platform ships: lowered to IS NOT NULL (true) / IS NULL (false) on the SQL family and to { $ne: null } (true) / { $eq: null } (false) on MongoDB. |
| $empty | boolean | optional | Is-empty check by the field's DECLARED type. true matches rows whose field is empty, false is its exact complement. What counts as empty: text-like types (text, textarea, email, url, phone, password, secret, markdown, html, richtext, code, color, signature, qrcode) = null or '' (the empty string); multi-value types (multiselect, checkboxes, tags, and select, radio, lookup, user, file or image with multiple: true) = null or [] (the empty list); every other type = null only. A face that holds no field declaration judges by the value: null, '' and [] are empty. A face that answers by the declared type refuses the operator on a column whose declaration it does not hold (the built-in id, for one) rather than guess a row; use $null there for "has no value". The view operators is_empty / is_not_empty lower to this operator. |
StringOperator
Properties
| Property | Type | Required | Description |
|---|---|---|---|
| $contains | string | optional | Contains substring, CASE-SENSITIVELY — "acme" does NOT match "ACME". Lowered to LIKE '%?%' (case-exact) on the SQL family, and answered case-exactly on every JS evaluation face the platform ships. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). Case-INSENSITIVE containment is $icontains, which folds ASCII case only. |
| $notContains | string | optional | Does not contain substring, CASE-SENSITIVELY — the negation of $contains, on the same comparand contract. Lowered to NOT LIKE '%?%' (case-exact) on the SQL family. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). |
| $startsWith | string | optional | Starts with prefix, CASE-SENSITIVELY. Lowered to LIKE '?%' (case-exact) on the SQL family. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). |
| $endsWith | string | optional | Ends with suffix, CASE-SENSITIVELY. Lowered to LIKE '%?' (case-exact) on the SQL family. The comparand is matched LITERALLY: "%", "_" and regex metacharacters are ordinary characters, because this family escapes and anchors the comparand on the caller's behalf. To bind the wildcards yourself, write $like (case-exact) or $ilike (ASCII-folded). |
| $icontains | string | optional | Contains substring, ignoring case — but ONLY ASCII case (A-Z against a-z). Every other character compares literally, so "café" does NOT match "CAFÉ" and "москва" does not match "МОСКВА". The domain is ASCII because that is the one fold all five backends can deliver: SQLite (and therefore turso and sqlite-wasm) folds ASCII only, so a Unicode promise here would be a guarantee three of the five could not keep. The comparand is matched LITERALLY — "%", "_" and regex metacharacters are ordinary characters, not wildcards. Case-SENSITIVE containment is $contains. Lowered on the SQL family (driver-sql, driver-sqlite-wasm, driver-turso on both transports) and on every JS evaluation face, so it is portable across every backend the platform ships. |
| $like | string | optional | Whole-string pattern match with CALLER-bound wildcards: "%" matches any sequence (including empty), "" matches exactly one character, and a character is one Unicode code point — an emoji or any other character outside the Basic Multilingual Plane is ONE "", never two, as SQL LIKE counts it. A backslash escapes the character after it ("\%", "\_", "\\") so it matches literally. The pattern must cover the WHOLE value — a pattern with no wildcards is an exact comparison, NOT a substring search; write $contains for containment. A pattern ending in a lone unpaired backslash, or holding the NUL character U+0000, is refused (INVALID_FILTER). Comparison is case-SENSITIVE, same contract as $contains (Q2 = A); $ilike is the case-insensitive twin. Answered by the SQL family (driver-sql, driver-sqlite-wasm, driver-turso on both transports), by driver-memory and by @objectstack/formula. driver-mongodb, objectql having and service-analytics REFUSE it in the INVALID_FILTER envelope rather than approximating it — see FILTER_OPERATORS for why it is staged out of that allowlist. |
| $ilike | string | optional | Whole-string pattern match like $like — "%" / "_" wildcards bound by the caller, backslash escapes — but ignoring ASCII case (A-Z against a-z) and ONLY ASCII case: "café" does NOT match "CAFÉ", the same Q1 = A boundary $icontains declares, because SQLite's fold is ASCII-only and three of the five backends are SQLite underneath. Staged with $like — see FILTER_OPERATORS. |