WHERE Operators
Look up every filter operator for each column type, with examples.Operator reference by column type
Operator support by column type (TypeScript):
| Column kind | Operators / shape |
|---|---|
id | eq, ne, in |
string | eq, ne, contains, in |
boolean | eq, ne, in |
int / float | eq, ne, gt, gte, lt, lte, in |
timestamp | eq, ne, gt, gte, lt, lte, in |
bytes | eq, ne, in |
uuid (required) | eq, ne, in |
uuid? | eq, ne, in, isNull |
enum | eq, ne, in |
array | eq, contains, in |
json | eq, ne, in |
Nullable columns with ordinary filter operators also support isNull. Required columns and id do not expose it.
Examples
Equality and inequality
// Exact match (shorthand — no operator object needed)const incompleteTodos = await db.all(app.todos.where({ done: false }));// Not equalconst nonDraftTodos = await db.all(app.todos.where({ title: { ne: "Draft" } }));// One of a setconst selectedTodos = await db.all(app.todos.where({ id: { in: [todoIdA, todoIdB] } }));
// Exact match (shorthand — no operator object needed)const incompleteTodos = yield* jazz.all(app.todos.where({ done: false }));// Not equalconst nonDraftTodos = yield* jazz.all(app.todos.where({ title: { ne: "Draft" } }));// One of a setconst selectedTodos = yield* jazz.all(app.todos.where({ id: { in: [todoIdA, todoIdB] } }));
// Exact matchlet incomplete_todos = jazz.all(todos::query().filter(todos::done().eq(false))).await?;// Not equallet non_draft_todos = jazz.all(todos::query().filter(todos::title().ne("Draft"))).await?;// One of a setlet selected_todos = jazz.all(todos::query().filter(todos::id().is_in([todo_id_a, todo_id_b]))).await?;
let incomplete = try await jazz.all(todos.query().where(todos.done.eq(false)))let nonDraft = try await jazz.all(todos.query().where(todos.title.ne("Draft")))let selected = try await jazz.all(todos.query().where(todos.id.in([todoIdA, todoIdB])))
// Exact matchval incompleteTodos = jazz.all(Todos.where(Todos.done eq false))// Not equalval nonDraftTodos = jazz.all(Todos.where(Todos.title ne "Draft"))// One of a setval selectedTodos = jazz.all(Todos.where(Todos.id inList listOf(todoIdA, todoIdB)))
incompleteTodos, err := jazzapp.Todos.Query().Where(jazzapp.Todos.Done.Eq(false)).All(ctx, db)nonDraftTodos, err := jazzapp.Todos.Query().Where(jazzapp.Todos.Title.Ne("Draft")).All(ctx, db)selectedTodos, err := jazzapp.Todos.Query().Where(jazzapp.Todos.ID.In(todoIDA, todoIDB)).All(ctx, db)
Numeric comparisons
const oneWeekAgo = Date.now() - 7 * 24 * 60 * 60 * 1000;const recentTodos = await db.all(app.todos.where({ $createdAt: { gt: oneWeekAgo } }));const highPriority = await db.all(app.todos.where({ priority: { gte: 3 } }));const lowPriority = await db.all(app.todos.where({ priority: { lt: 10 } }));
const oneWeekAgo = Date.now() - 7 * 24 * 60 * 60 * 1000;const recentTodos = yield* jazz.all(app.todos.where({ $createdAt: { gt: oneWeekAgo } }));const highPriority = yield* jazz.all(app.todos.where({ priority: { gte: 3 } }));const lowPriority = yield* jazz.all(app.todos.where({ priority: { lt: 10 } }));
// Every table has `created_at()` and `updated_at()` columns.let one_week_ago = Timestamp(Timestamp::now().0 - 7 * 24 * 60 * 60 * 1000);let recent_todos = jazz.all(todos::query().filter(jazz_app::created_at().gt(one_week_ago))).await?;let high_priority = jazz.all(todos::query().filter(todos::priority().gte(3))).await?;let low_priority = jazz.all(todos::query().filter(todos::priority().lt(10))).await?;
// Magic columns such as `$createdAt` (a `Date`) have no binding: name them.let createdAt = Jazz.Column<JazzApp.Todo, Date>("$createdAt")let oneWeekAgo = Date().addingTimeInterval(-7 * 24 * 60 * 60)let recent = try await jazz.all(todos.query().where(createdAt.gt(oneWeekAgo)))let highPriority = try await jazz.all(todos.query().where(todos.priority.gte(3)))let lowPriority = try await jazz.all(todos.query().where(todos.priority.lt(10)))
val oneWeekAgo = System.currentTimeMillis() - 7 * 24 * 60 * 60 * 1000Lval recentTodos = jazz.all(Todos.where(JazzColumns.createdAt gt oneWeekAgo))val highPriority = jazz.all(Todos.where(Todos.priority gte 3))val lowPriority = jazz.all(Todos.where(Todos.priority lt 10))
createdAt := jazz.NewColumn[jazzapp.Todo, jazz.Timestamp]("$createdAt") // a magic columnoneWeekAgo := jazz.TimestampOf(time.Now().Add(-7 * 24 * time.Hour))recentTodos, err := jazzapp.Todos.Query().Where(createdAt.Gt(oneWeekAgo)).All(ctx, db)highPriority, err := jazzapp.Todos.Query().Where(jazzapp.Todos.Priority.Gte(3)).All(ctx, db)lowPriority, err := jazzapp.Todos.Query().Where(jazzapp.Todos.Priority.Lt(10)).All(ctx, db)
String contains
// Substring match (case-sensitive)const matches = await db.all(app.todos.where({ title: { contains: searchTerm } }));
// Substring match (case-sensitive)const matches = yield* jazz.all(app.todos.where({ title: { contains: searchTerm } }));
// Substring match (case-sensitive)let matches = jazz.all(todos::query().filter(todos::title().contains(search_term))).await?;
// Substring match (case-sensitive).let matches = try await jazz.all(todos.query().where(todos.title.contains(searchTerm)))
// Substring match (case-sensitive)val matches = jazz.all(Todos.where(Todos.title contains searchTerm))
// Substring match (case-sensitive).matches, err := jazzapp.Todos.Query().Where(jazzapp.Todos.Title.Contains(searchTerm)).All(ctx, db)
Null checks on optional columns
In TypeScript, isNull: true matches missing values and isNull: false matches present values. Nullable columns with ordinary filter operators support it, including scalar and array columns, not just references; required columns do not expose it.
// Rows where the optional ref is not setconst unlinkedTodos = await db.all(app.todos.where({ parentId: { isNull: true } }));// Rows where it is setconst linkedTodos = await db.all(app.todos.where({ parentId: { isNull: false } }));
// Rows where the optional ref is not setconst unlinkedTodos = yield* jazz.all(app.todos.where({ parentId: { isNull: true } }));// Rows where it is setconst linkedTodos = yield* jazz.all(app.todos.where({ parentId: { isNull: false } }));
// Rows where the optional ref is not setlet unlinked_todos = jazz.all(todos::query().filter(todos::parent_id().is_null())).await?;// Rows where it is setlet linked_todos = jazz.all(todos::query().filter(todos::parent_id().is_not_null())).await?;
// Rows where the optional reference is not set, and where it is.let unlinked = try await jazz.all(todos.query().where(todos.parentId.isNull()))let linked = try await jazz.all(todos.query().where(todos.parentId.isNotNull()))
// Rows where the optional ref is not setval unlinkedTodos = jazz.all(Todos.where(Todos.parentId.isNull()))// Rows where it is setval linkedTodos = jazz.all(Todos.where(Todos.parentId.isNotNull()))
// Rows where the optional reference is not set, and where it is.unlinkedTodos, err := jazzapp.Todos.Query().Where(jazzapp.Todos.ParentID.IsNull()).All(ctx, db)linkedTodos, err := jazzapp.Todos.Query().Where(jazzapp.Todos.ParentID.IsNotNull()).All(ctx, db)
Multiple conditions (AND)
All predicates passed to where(...) / chained filter_* calls are AND-combined:
// done AND assigned to a projectconst doneWithProject = await db.all(app.todos.where({done: true,projectId: { isNull: false },}),);
// done AND assigned to a projectconst doneWithProject = yield* jazz.all(app.todos.where({done: true,projectId: { isNull: false },}),);
// Multiple filter calls are AND-combined: done AND assigned to a projectlet done_with_project = jazz.all(todos::query().filter(todos::done().eq(true)).filter(todos::project_id().is_not_null()),).await?;
// Conditions passed to `where` are ANDed.let doneWithProject = try await jazz.all(todos.query().where(todos.done.eq(true), todos.projectId.isNotNull()))
// done AND assigned to a projectval doneWithProject = jazz.all(Todos.where(Todos.done eq true,Todos.projectId.isNotNull(),),)
// Done and assigned to a project: conditions in one Where all hold.doneWithProject, err := jazzapp.Todos.Query().Where(jazzapp.Todos.Done.Eq(true), jazzapp.Todos.ProjectID.IsNotNull()).All(ctx, db)
Combining with ordering and limits
const recentIncomplete = await db.all(app.todos.where({ done: false }).orderBy("$createdAt", "asc").limit(50),);
const recentIncomplete = yield* jazz.all(app.todos.where({ done: false }).orderBy("$createdAt", "asc").limit(50),);
let recent_incomplete = jazz.all(todos::query().filter(todos::done().eq(false)).order_by(jazz_app::created_at(), Order::Asc).limit(50),).await?;
let recentIncomplete = try await jazz.all(todos.query().where(todos.done.eq(false)).orderBy(Jazz.Column<JazzApp.Todo, Date>("$createdAt").asc).limit(50))
val recentIncomplete = jazz.all(Todos.where(Todos.done eq false).orderBy(JazzColumns.createdAt.asc()).limit(50),)
recentIncomplete, err := jazzapp.Todos.Query().Where(jazzapp.Todos.Done.Eq(false)).OrderBy(jazz.NewColumn[jazzapp.Todo, jazz.Timestamp]("$createdAt").Asc()).Limit(50).All(ctx, db)
Live subscriptions with WHERE
useAll and query subscriptions accept the same query builders as db.all. The subscription stays active and updates whenever any row enters or exits the filter:
export function subscribeOpenTodos(db: Db, onChange: (todos: unknown[]) => void) {return db.subscribe(app.todos.where({ done: false }), (todos) => onChange(todos));}
// A Stream of the open todos, re-emitted whenever they change.export const openTodos = Stream.unwrap(Jazz.useSync((jazz) => jazz.stream(app.todos.where({ done: false }))),);
pub async fn subscribe_open_todos(jazz: &Jazz) -> jazz_app::Result<Subscription<Todo>> {// Dropping the subscription unsubscribes.jazz.subscribe(todos::query().filter(todos::done().eq(false))).await}
// Iterate it (`for try await todos in subscription`) to get each result.let subscription = jazz.watch(todos.query().where(todos.done.eq(false)))
// Collecting the flow subscribes; each value is the whole result, updated// whenever a todo enters or leaves the filter.fun watchOpenTodos(jazz: Jazz): Flow<List<Todo>> =jazz.watch(Todos.where(Todos.done eq false))
func SubscribeOpenTodos(ctx context.Context, db *jazz.DB) (*jazz.Live[jazzapp.Todo], error) {return jazzapp.Todos.Query().Where(jazzapp.Todos.Done.Eq(false)).Live(ctx, db)}
For reactive framework bindings (useAll in React/Vue/Solid, QuerySubscription in Svelte), see Framework Patterns.