Queries & Filters

Every query in Atscript's DB layer follows the same shape: a filter that selects which records to return, and a controls object that determines how they come back (sorting, pagination, projection). This syntax is consistent across all adapters.

typescript

const results = await table.findMany({
  filter: {
    /* which records */
  },
  controls: {
    /* how to return them */
  },
});

Filter Syntax

Filters use a MongoDB-inspired expression language. At its simplest, you pass an object whose keys are field names and whose values are the conditions to match.

Equality

The most common filter is a direct equality check:

typescript

// Shorthand — value is the match target
{
  filter: {
    status: "active";
  }
}

// Explicit operator form
{
  filter: {
    status: {
      $eq: "active";
    }
  }
}

Multiple fields in the same object are combined with AND:

typescript

{ filter: { status: 'active', role: 'admin' } }
// WHERE status = 'active' AND role = 'admin'

Not Equal

typescript

{
  filter: {
    status: {
      $ne: "done";
    }
  }
}
// WHERE status != 'done'

Comparisons

typescript

{
  filter: {
    age: {
      $gt: 18;
    }
  }
} // greater than
{
  filter: {
    age: {
      $gte: 18;
    }
  }
} // greater than or equal
{
  filter: {
    age: {
      $lt: 65;
    }
  }
} // less than
{
  filter: {
    age: {
      $lte: 65;
    }
  }
} // less than or equal

These operators are available on number, string, and Date fields.

Set Operators

Check whether a value belongs (or does not belong) to a set:

typescript

{
  filter: {
    role: {
      $in: ["admin", "editor"];
    }
  }
}
// WHERE role IN ('admin', 'editor')

{
  filter: {
    status: {
      $nin: ["archived", "deleted"];
    }
  }
}
// WHERE status NOT IN ('archived', 'deleted')

Pattern Matching

typescript

{
  filter: {
    name: {
      $regex: "^Al";
    }
  }
}
// SQLite/PostgreSQL: WHERE name LIKE 'Al%' (or REGEXP)
// MongoDB: WHERE name matches /^Al/

$regex is available on string fields and accepts a RegExp or string. On an integer field (number.int and its sizes, number + @expect.int, @db.default.increment) it matches the number's decimal text — { refNo: { $regex: "^29" } } finds 29461277 — identically on every adapter (a full scan). Floats, decimals and timestamps are refused with INVALID_QUERY; filter them by range.

Existence

$exists tests whether a field holds a value — the same answer on every adapter:

typescript

{
  filter: {
    email: {
      $exists: true;
    }
  }
} // SQL: WHERE email IS NOT NULL
{
  filter: {
    email: {
      $exists: false;
    }
  }
} // SQL: WHERE email IS NULL

Stored value	`$exists: true`	`$exists: false`
Missing / `undefined`	—	match
Explicit `null`	—	match
Any other value, including `{}`, `[]`, `""`, `0`, `false`	match	—

Rules (since 0.1.132):

The operand must be a boolean. { $exists: 1 } or { $exists: "false" } throws DbError("INVALID_QUERY") — $exists on "email" expects true or false (HTTP 400). An @db.encrypted field fails with ENC_FIELD_FILTER first, as for any filter.
Works on any stored column, including @db.json objects and arrays on SQL adapters, which otherwise reject every filter on the column. Only an entry whose sole operator is $exists qualifies: { metrics: { $exists: true } } is accepted, { metrics: { $exists: true, $ne: null } } or { metrics: { value: 1 } } is not — the error then names (accepted operators: $exists). Each occurrence inside $and / $or / $not is judged on its own, so an $exists entry never unlocks another entry on the same path.
Descendants stay rejected. { "metrics.value": { $exists: true } } on a SQL JSON column, a flattened object parent (contact) and navigation paths fail like any other filter (see the path rules below). $sort, $groupBy, $having and aggregate fields are unaffected.

MongoDB and memory: null counts as absent since 0.1.132

Before 0.1.132 the MongoDB and memory adapters answered $exists by key presence, so a document storing note: null matched $exists: true. They now match SQL (where a missing value and NULL are the same thing): MongoDB translates { f: { $exists: true } } to { f: { $ne: null } } and $exists: false to { f: null }. Filters that relied on "key present even if null" now return fewer rows (with $exists: true) or more (with false). Key presence is no longer expressible through the portable filter — { note: null } also matches a missing key — so use the native collection if you need it.

Null Values

You can also filter for null directly:

typescript

{
  filter: {
    assigneeId: null;
  }
}
// WHERE assigneeId IS NULL

Since 0.1.128 the filter types admit this: the readable's flat and own-props shapes are wrapped in NullableOptional, so every optional property accepts null in bare, $eq, $ne and $in positions ({ note: null }, { note: { $ne: null } } type-check; null on a required property does not). $in: [null] never matches on SQL adapters (IN (NULL)), use the bare form. Optional columns read back as null (SQL) or are absent (MongoDB) — compare with == null.

Value Types

Since 0.1.147 every comparison value — a bare value, $eq, $ne, $gt, $gte, $lt, $lte, each $in / $nin element — must be able to stand for the field's declared type. A value that cannot is rejected before the query reaches the database, with DbError("INVALID_QUERY") (HTTP 400 through moost-db) whose path is the field:

typescript

await tasks.findMany({ filter: { priority: { $gte: "high" } } });
// DbError INVALID_QUERY — path "priority":
// Invalid filter value for "priority" ($gte): expected a number, got "high"

Field type	Accepted values
`number`	a number or a numeric string (`5`, `"5"`, `"-1.5"`, `"1e3"`)
an integer field (below)	a whole number or an integer string (`5`, `"5"`) — not `5.5`. Timestamps are epoch milliseconds, not ISO date strings
`decimal`	a number or a numeric string (`"12.50"`)
`boolean`	`true` / `false`, `0` / `1`
`string`, every `string.*`, string literals	a string, number or boolean
a union (`number \| boolean`)	what any member accepts
an array (`string[]`, MongoDB and memory)	its element type — for an array operand, every element

Integer fields are number.int and its sizes, number.timestamp, a number with @db.default.increment or @db.default.now, and a view's @db.agg.count / @db.agg.countDistinct column.
Non-finite numbers are rejected (since 0.1.148): NaN, Infinity and -Infinity — bare or inside $in / $nin — fail with expected a number, got NaN on number and decimal fields. A numeric string such as "Infinity" is rejected too.
Always accepted: null ({ f: null }, { f: { $ne: null } }) and class instances such as Date or a MongoDB ObjectId.
Never checked: @db.json fields and their contents, nested-object parents, db.geoPoint and $exists (a boolean, see Existence).
Literal unions are checked by their primitive type, not by membership: { status: "unknown" } on 'open' | 'closed' is valid and matches nothing.
$regex (and a bare RegExp value) needs a field that holds strings — or an integer field, which matches the number's decimal text — and a string or RegExp pattern. A float, decimal or timestamp field is refused.
Accepted values are passed on unchanged. A numeric string compares as a number on the SQL adapters but matches nothing on MongoDB and memory, which compare types strictly — pass values of the declared type.

The same check covers view columns (aggregates and computed columns, by their declared type), $having (aggregate aliases: numbers, counts: integers; min / max: the source field's type; calendar-bucket labels: strings), the operands of relational filters (the path is prefixed with the relation, issues.severity) and the filters of updateMany / deleteMany. An unknown or unfilterable field keeps its own error (see Nested Field Filters).

Logical Operators

Implicit AND

When you put multiple fields in a single filter object, they are ANDed together automatically:

typescript

{ filter: { status: 'active', role: 'admin' } }

Explicit AND

Use $and when you need multiple conditions on the same field, or just prefer being explicit:

typescript

{
  filter: {
    $and: [{ age: { $gte: 18 } }, { age: { $lt: 65 } }];
  }
}

OR

typescript

{
  filter: {
    $or: [{ status: "active" }, { role: "admin" }];
  }
}

On the SQL adapters an $or whose branches include several equalities on one field ({ id: 1 }, { id: { $eq: 2 } }, { id: { $in: [3, 4] } }) is rendered as one IN (…) (since 0.1.165) — same rows, but a per-row key filter over a large batch no longer exceeds SQLite's expression-depth limit. Composite branches ({ a: 1, b: 2 }) keep their OR: for a large batch of composite keys, chunk the filter yourself.

NOT

Negate a set of conditions:

typescript

{
  filter: {
    $not: {
      status: "archived";
    }
  }
}

Nested Combinations

Logical operators compose naturally:

typescript

{ filter: {
  $and: [
    { $or: [
      { priority: 'high' },
      { priority: 'critical' },
    ] },
    { $not: { status: 'done' } },
  ],
} }

Nested Field Filters

Atscript automatically flattens nested objects into __-separated column names (e.g., a contact.email field becomes the contact__email column). When filtering, use dot notation — the adapter translates it to the physical column name:

typescript

{ filter: { 'contact.email': 'alice@example.com' } }
// SQL: WHERE contact__email = 'alice@example.com'

{ filter: { 'address.city': { $in: ['Berlin', 'Paris'] } } }
// SQL: WHERE address__city IN ('Berlin', 'Paris')

This works with all operators — comparisons, $regex, $exists, and logical combinators.

Paths are validated before translation (since 0.1.128)

Every filter key, $sort key, $select entry, $groupBy field, $having key and aggregate $field must resolve to physical storage on the adapter in use, or the call throws DbError("INVALID_QUERY") (HTTP 400 through moost-db) before any SQL or pipeline is built:

physical column names (contact__email, @db.column-renamed names) are no longer accepted — use logical paths;
descendants of a @db.json / array column (prefs.theme) are rejected on SQL adapters (MongoDB and memory address them natively);
a @db.json / array column itself accepts only an $exists entry on SQL adapters (since 0.1.132; before, every filter on it was rejected);
navigation paths (assignee.name) are rejected — load relations with $with, or filter by them with a relational predicate ({ assignee: { $some: { name: … } } }, since 0.1.147);
a flattened object parent (contact) can be selected but not sorted, and filtered only by a null test — { contact: null }, { contact: { $ne: null } }, { contact: { $exists: … } } (since 0.1.155, see Nullable and Union Fields); compare a leaf otherwise;
$sort on a JSON / array column is rejected on every adapter (canSortField);
filter nodes may only carry $and, $or, $not — $nor is rejected.
$having keys must be aggregate aliases ($as, else fn_field) or $groupBy fields — a real but non-grouped column throws $having key "region" must be an aggregate alias or a $groupBy field (before 0.1.128 PostgreSQL / MySQL raised an engine error, SQLite ignored the key and MongoDB returned no rows).

This includes updateMany / deleteMany filters and transformFilter overlays in moost-db.

Relational Filters (`$some` / `$none`)

Since 0.1.147. Select rows by their related rows — "issues whose ticket is open", "tickets without issues" — by putting an operator map on a navigation property. The examples in this section use this schema:

atscript

@db.table 'teams'
export interface Team {
    @meta.id
    id: string
    name: string
}

@db.table 'tickets'
export interface Ticket {
    @meta.id
    key: string
    status: string

    @db.rel.FK
    teamId?: Team.id

    @db.rel.to
    team?: Team

    @db.rel.from
    issues: Issue[]

    @db.rel.via TicketLabel
    labels: Label[]
}

@db.table 'issues'
export interface Issue {
    @meta.id
    id: number
    title: string
    status?: string

    @db.rel.FK
    ticketKey?: Ticket.key

    @db.rel.to
    ticket?: Ticket
}

(Label and the TicketLabel junction follow the many-to-many pattern.)

typescript

// Issues whose ticket is open and belongs to team t1 or t2
await issues.findMany({
  filter: { ticket: { $some: { status: "open", teamId: { $in: ["t1", "t2"] } } } },
});

// Tickets without any issue
await tickets.findMany({ filter: { issues: { $none: {} } } });

// Tickets labelled "bug" but not "wontfix"
await tickets.findMany({
  filter: { labels: { $some: { name: "bug" }, $none: { name: "wontfix" } } },
});

Operator	A row matches when	With `{}`
`$some: F`	at least one related row matches `F`	it has any related row
`$none: F`	no related row matches `F`	it has no related row

Several operators on one key are ANDed. Predicates combine with other conditions and with $and / $or / $not like any field condition.

The related rows of a row are exactly the rows $with loads for it — the same foreign-key pairing (aliases included) and the relation's @db.rel.filter. The meaning is the same for to-one and to-many relations:

Relation	Related rows of a row `r`
`@db.rel.to`	the target row whose key equals `r`'s foreign key
`@db.rel.from`	the target rows whose foreign key references `r`
`@db.rel.via`	the target rows linked to `r` by a row of the junction table

Composite keys must match on every part.

NULL foreign keys. A row whose foreign key is null (any part of a composite one) has no related row: $some never matches it and $none always does. { ticket: { $none: { status: "open" } } } therefore also returns issues without a ticket — add ticketKey: { $ne: null } when you mean "has a ticket, and it is not open". Both operators are plain true / false tests on every engine, so the SQL adapters, MongoDB and the memory adapter return the same rows.

Writing the operand

Keys are the related table's logical field paths (status, contact.email), not prefixed with the relation name. Every filter operator works.
The related table's own rules apply inside: unknown fields, @db.encrypted fields (ENC_FIELD_FILTER), JSON descendants on SQL adapters and the other path rules are rejected, with the path prefixed by the relation (ticket.note).

To cross another relation, nest a predicate — one relation per level:

typescript

// Issues whose ticket belongs to the team named "Core"
{
  ticket: {
    $some: {
      team: {
        $some: {
          name: "Core";
        }
      }
    }
  }
}

A dotted navigation path is still rejected, with a hint: Cannot filter on "ticket.status" — navigation path; use { ticket: { $some: { status: … } } }.
$some / $none cannot share a key with a comparison operator (Cannot mix "$some" / "$none" with "$eq" on "ticket"), and each takes an object ("$some" on "ticket" expects a filter object).

"Every related row" — no `$every`

There is no $every operator. "Every related row matches F" is "no related row fails F": { nav: { $none: { $not: F } } }. It is also true when there are no related rows; add $some: {} on the same key to require at least one.

When F reads an optional field, write the negation yourself. A null inside $not is not handled the same way everywhere: SQL treats NOT (status = 'closed') on a NULL status as unknown (the row does not fail F), while MongoDB and the memory adapter treat it as a mismatch (the row fails F). Spell the failing case out so every adapter agrees:

typescript

// Tickets whose issues are all closed (a null status counts as not closed)
await tickets.findMany({
  filter: { issues: { $none: { $or: [{ status: { $ne: "closed" } }, { status: null }] } } },
});

Where predicates are accepted

The filter of findOne, findMany, count, findManyWithCount, the text / vector / geo search methods, and aggregate() (the row filter — never $having).
$with sub-filters, for the related table's own relations — see Loading Relations.
Mutation filters: updateMany, replaceMany, deleteMany, and a row scope. Their filter type (MutationFilter) accepts predicates on nav keys like the read filter (since 0.1.165; before, deleteMany({ issues: { $some: … } }) failed to typecheck).

Server-side code needs no opt-in. HTTP clients do, per relation — see Permissions § Relational predicates.

All five bundled adapters run predicates in reads and writes. How each one executes them is on its page: PostgreSQL, MySQL, SQLite, MongoDB, memory.

Index the join columns

Each predicate runs a correlated lookup per candidate row. Index the foreign-key column on the @db.rel.from side (issues.ticketKey for tickets.issues) and both foreign-key columns of a @db.rel.via junction. A @db.rel.to predicate looks up the target's primary or unique key, which is indexed already.

Limits and errors

Condition	Error
more than 4 nested levels (`REL_FILTER_MAX_DEPTH`)	`INVALID_QUERY` — `Relational predicates nest at most 4 levels deep` (no `path`)
more than 16 predicates in one filter, nested ones included (`REL_FILTER_MAX_NODES`)	`INVALID_QUERY` — `At most 16 relational predicates per query` (no `path`)
`$some` / `$none` on a field that is not a navigation property	`INVALID_QUERY` — `"$some" / "$none" are only valid on a navigation relation — "title" is not one`
a self-referencing many-to-many (a junction with one FK to the type)	`INVALID_QUERY` — its two junction keys cannot be told apart
the adapter does not support predicates (custom adapters by default)	`REL_FILTER_NOT_SUPPORTED` — `… not supported by this adapter` (`… in mutation filters` for writes)
the related table lives in another database or on another adapter (`sharesStoreWith`)	`REL_FILTER_NOT_SUPPORTED`
the table was not created through a `DbSpace`	`REL_FILTER_NOT_SUPPORTED` — the related table cannot be resolved

The error path is the dotted relation chain (ticket.team); messages name relations, never physical table names. Both codes answer HTTP 400 through moost-db. The limits are exported from @atscript/db as REL_FILTER_MAX_DEPTH and REL_FILTER_MAX_NODES.

These core limits count every predicate of the filter, server-added ones included (row scopes, transformRelationFilter overlays), so their errors name no path. HTTP clients have their own, lower budget — 3 levels and 8 predicates per request (REL_FILTER_CLIENT_MAX_DEPTH, REL_FILTER_CLIENT_MAX_NODES, exported from @atscript/moost-db), counting the client's predicates only — which leaves the server headroom for its overlays. See Permissions § Relational predicates.

Typing

Typed filters accept a predicate on navigation keys only: { ticket: { $some: … } } type-checks, { title: { $some: {} } } and unknown keys do not. The operand of a relation to an .as type is not typed field by field (Record<string, unknown>) — it is validated at run time.

Query Controls

The controls object determines how the result set is shaped.

Sorting

Use $sort with 1 for ascending and -1 for descending:

typescript

controls: {
  $sort: {
    name: 1;
  }
} // A → Z
controls: {
  $sort: {
    createdAt: -1;
  }
} // newest first

Multiple sort keys are applied in order:

typescript

controls: { $sort: { status: 1, name: -1 } }
// ORDER BY status ASC, name DESC

NULL placement

Without a placement, where null / missing values land follows the engine: SQLite, MySQL, MongoDB and the memory adapter put them first in ascending order (last in descending); PostgreSQL puts them last in ascending order (first in descending).

Since 0.1.153, $nulls pins them per sort key. 'first' puts them before every value and 'last' after every value, in both directions and on every adapter:

typescript

await tickets.findMany({
  controls: { $sort: { closedAt: -1, title: 1 }, $nulls: { closedAt: "last" } },
});
// newest closed first, open tickets (closedAt = null) at the end

One map for every ordered key. An entry applies to the $sort key of the same name, and in grouped queries also to a computed alias in $sort and to the $rowOrder key of first() / last(). Entries for keys the query does not order by are ignored. Values other than 'first' / 'last' fail with INVALID_QUERY.
Field default. @db.sort.nulls 'first' | 'last' on a field applies whenever a query sorts by it without a $nulls entry. An explicit entry overrides it.
Required fields are skipped. A table field that cannot be null (required, not | null, and no optional or | null parent object; since 0.1.155 a | null union counts as nullable) has nothing to place, so its entry is dropped and the plain ORDER BY keeps using the index. Views keep every entry, because a left join or an aggregate can make any view column null.
Keys without an entry are unchanged. $nulls is opt-in: a query without it sorts exactly as before. Grouped $sort and $rowOrder keep their existing "NULL is the smallest value" order unless an entry overrides it.
Over HTTP, write the placement as a suffix on the sort key: $sort=-closedAt:last,title. See URL query syntax.

Index use

A placement equal to the engine's own (for example 'first' ascending on SQLite, MySQL, MongoDB; 'last' ascending on PostgreSQL) costs nothing. The other placement may not follow the index order:

Engine	Non-native placement
PostgreSQL	`NULLS FIRST` / `NULLS LAST`: a sort step unless an index is built with the same NULLS order
SQLite	`NULLS FIRST` / `NULLS LAST`: the bundled SQLite (3.51) still reads the index in order — no extra cost
MySQL	`(col IS NULL) ASC` / `DESC` in front of the key: always a filesort
MongoDB	the read runs as an aggregation pipeline with a computed null flag: a blocking sort, no index
Memory	no difference

On large PostgreSQL, MySQL and MongoDB tables, prefer the native placement for hot sort keys (or narrow the rows with a filter first). On PostgreSQL an index built with the matching NULLS FIRST / NULLS LAST order also serves it.

Adapters that do not implement NULL placement (third-party adapters without supportsNullsPlacement(), see Creating adapters) reject an explicit $nulls with INVALID_QUERY and ignore @db.sort.nulls defaults.

Ties and the primary-key tie-breaker

Since 0.1.153, rows that tie on every $sort key come back in a fixed order: the primary key is added to the end of the sort, so the same query returns the same order every time, and $skip / $limit pages never repeat or skip a row.

typescript

await tasks.findMany({
  controls: { $sort: { status: 1, priority: -1 }, $skip: 40, $limit: 20 },
});
// ORDER BY status ASC, priority DESC, id DESC

Direction. The added fields take the direction of the last $sort key. A descending last key therefore needs only a backward scan of an index on (…, key, id).
Composite primary keys. Every primary-key field not already in $sort is added, in declaration order.
Nothing is added when the $sort already orders rows on its own: it names every primary-key field, or every field of a unique index whose fields are all required. A unique index over an optional field does not count, because several rows can hold null there.
Scope. It applies to findMany, findManyWithCount, findOne, and an explicit $sort on search / vectorSearch, as well as to the rows of a $with relation that has its own $sort. It does not apply to grouped queries ($groupBy, see Grouped Queries) or to count().
No $sort, no order. A query without $sort is not ordered. Text search keeps its relevance order, and vector and geo search keep their distance order. If offset paging must be stable, pass a $sort. The moost-db /pages endpoint does this for you and reads in primary-key order when the request has no $sort.
Views get the tie-breaker only when they declare a primary key with @meta.id on the view's own fields. Selecting the source table's key (id: Order.id) does not declare one. Without a declared key, ties keep the engine's order.

Pagination

typescript

controls: {
  $limit: 10,   // return at most 10 records
  $skip: 20,    // skip the first 20 records
}

Field Selection

Include specific fields using array form:

typescript

controls: {
  $select: ["id", "name", "email"];
}

Or exclude fields with an object where 0 means exclude:

typescript

controls: { $select: { password: 0, internalNotes: 0 } }

When selecting a nested object parent, all its child fields are included:

typescript

controls: {
  $select: ["id", "contact"];
}
// Includes contact.email, contact.phone, etc.

Computed entries

An include array can also carry aggregate entries ({ $fn: 'sum', $field: 'amount' }) and calendar buckets ({ $bucket: 'week', $field: 'createdAt' }). Both belong to grouped queries — see Grouped Queries. Any other non-string entry is rejected with INVALID_QUERY (Unsupported $select entry at index i) since 0.1.132; earlier versions dropped it silently.

FK Fields Auto-Included

When using $select with relation loading ($with), foreign key fields needed for relation resolution (e.g., assigneeId for an assignee relation) are automatically included even if not listed in $select.

Paginated Results

Use findManyWithCount() to get both data and total count in one call — see CRUD Operations — Find Many with Count for the API and examples.

Type-Safe Generics

Queries are fully typed. findOne and findMany accept a Uniquery<OwnProps, NavType> that constrains filter fields to own (non-navigation) properties — plus, since 0.1.147, relational predicates on navigation properties. The return type DbResponse automatically strips navigation properties from the result unless you request them via $with.

When the query type is a literal (not widened), TypeScript infers exactly which navigation properties are returned:

typescript

// result type includes `assignee` but not other nav props
const tasks = await taskTable.findMany({
  controls: { $with: [{ name: "assignee" }] },
});

Query Expressions

Query expressions are a compile-time syntax used inside .as files to define view filters, join conditions, and relation filters. They are not used in runtime TypeScript queries — they are embedded in annotations and compiled into the schema.

Syntax

Expressions are wrapped in backticks inside .as files:

atscript

@db.view.filter `Task.status != 'done'`

Field References

Reference fields using TableName.fieldName (a nested field as TableName.address.city):

atscript

@db.view.filter `Task.priority = 'high'`
@db.view.joins Project, `Project.id = Task.projectId`

An unqualified name (status) reads the annotation's default type: the entry table in @db.view.filter, @db.view.joins and @db.agg.* conditions, the related type in @db.rel.filter. @db.view.having takes only unqualified names — the view's own fields. See Annotations → Editor support for each annotation's scope.

Operators

Operator	Meaning	Example
`=`	equals	`Task.status = 'active'`
`!=`	not equals	`Task.status != 'done'`
`>`	greater than	`Task.priority > 3`
`>=`	greater than or equal	`Task.priority >= 3`
`<`	less than	`Task.age < 65`
`<=`	less than or equal	`Task.age <= 65`
`in`	in a list of values	`Task.status in ('active', 'pending')`
`not in`	not in a list of values	`Task.role not in ('guest', 'bot')`
`exists`	holds a value	`Task.assigneeId exists`
`not exists`	null or missing	`Task.deletedAt not exists`
`matches`	regex match	`User.name matches /^al/i`

The right side of a comparison is a literal — a string in single or double quotes, a number, true, false or null — or another field reference (`Project.id = Task.projectId`). in / not in take a parenthesized list of literals. matches takes a regex literal, /pattern/flags; view predicates reject it at sync on SQL adapters and in MongoDB join conditions (see View Filters), and @db.agg.* conditions don't accept it.

Logical Combinators

Combine conditions with the keywords and, or and not. not binds tightest, then and, then or; use parentheses for grouping. The symbolic forms &&, || and ! are not accepted — they are a compile error.

atscript

@db.view.filter `Task.status != 'done' and Task.priority >= 3`
@db.view.filter `(Task.status = 'active' or Task.status = 'pending') and Task.assigneeId exists`
@db.view.filter `not (Task.status = 'archived')`

Where They Are Used

Query expressions appear in these annotations:

@db.view.filter — row-level filter for a view
@db.view.joins — join condition between tables in a view
@db.view.having — having clause for aggregation views
@db.agg.* (second argument) — the rows a conditional aggregate reads
@db.rel.filter — static filter on a relation, applied when it is loaded and in relational predicates (since 0.1.147)

Example in a view definition:

atscript

@db.view
@db.view.for Task
@db.view.joins Project, `Project.id = Task.projectId`
@db.view.filter `Task.status != 'done' and Task.priority >= 3`
interface ActiveHighPriorityTasks {
  taskId: Task.id
  title: Task.title
  projectName: Project.name
}

Combining It All

A practical example that brings filters, sorting, pagination, and field selection together:

typescript

const tasks = await taskTable.findMany({
  filter: {
    status: { $ne: "done" },
    priority: { $in: ["high", "critical"] },
    project: { $some: { active: true } },
  },
  controls: {
    $sort: { priority: -1, createdAt: 1 },
    $limit: 20,
    $skip: 0,
    $select: ["id", "title", "status", "priority"],
  },
});

This returns the first 20 non-done tasks with high or critical priority from active projects, sorted by priority descending then creation date ascending, with only the selected fields.

Next Steps

CRUD Operations — Insert, read, update, delete
Grouped Queries — $groupBy, aggregates, $having, calendar buckets
Update & Patch — Embedded array and object patch operators
Views — Managed, external, and materialized views
Relations — Navigation property loading and deep operations

Queries & Filters ​

Filter Syntax ​

Equality ​

Not Equal ​

Comparisons ​

Set Operators ​

Pattern Matching ​

Existence ​

Null Values ​

Value Types ​

Logical Operators ​

Implicit AND ​

Explicit AND ​

OR ​

NOT ​

Nested Combinations ​

Nested Field Filters ​

Relational Filters ($some / $none) ​

Which rows are related ​

Writing the operand ​

"Every related row" — no $every ​

Where predicates are accepted ​

Limits and errors ​

Typing ​

Query Controls ​

Sorting ​

NULL placement ​

Ties and the primary-key tie-breaker ​

Pagination ​

Field Selection ​

Paginated Results ​

Type-Safe Generics ​

Query Expressions ​

Syntax ​

Field References ​

Operators ​

Logical Combinators ​

Where They Are Used ​

Combining It All ​

Next Steps ​

Queries & Filters

Filter Syntax

Equality

Not Equal

Comparisons

Set Operators

Pattern Matching

Existence

Null Values

Value Types

Logical Operators

Implicit AND

Explicit AND

OR

NOT

Nested Combinations

Nested Field Filters

Relational Filters (`$some` / `$none`)

Which rows are related

Writing the operand

"Every related row" — no `$every`

Where predicates are accepted

Limits and errors

Typing

Query Controls

Sorting

NULL placement

Ties and the primary-key tie-breaker

Pagination

Field Selection

Paginated Results

Type-Safe Generics

Query Expressions

Syntax

Field References

Operators

Logical Combinators

Where They Are Used

Combining It All

Next Steps