Filters, Sorting & Pagination
Learn how to filter rows with where(), sort them with orderBy() and page through them with limit and offset.Filters
Use where(...) to filter rows. Pass the columns you want to match on — all conditions are combined with AND. OR filters are not supported in queries; use multiple queries if you need disjoint result sets.
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.
export async function readTodosWithFilters(db: Db) {return db.all(app.todos.where({ done: false, title: { contains: "docs" } }));}
export async function readTodosWithWhereOperators(db: Db) {await db.all(app.todos.where({ done: false }));await db.all(app.todos.where({ title: { contains: "milk" } }));await db.all(app.todos.where({ projectId: { ne: EXAMPLE_PROJECT_ID } }));}
export const readTodosWithFilters = Effect.gen(function* () {const jazz = yield* Jazz;return yield* jazz.all(app.todos.where({ done: false, title: { contains: "docs" } }));});
export const readTodosWithWhereOperators = Effect.gen(function* () {const jazz = yield* Jazz;yield* jazz.all(app.todos.where({ done: false }));yield* jazz.all(app.todos.where({ title: { contains: "milk" } }));yield* jazz.all(app.todos.where({ projectId: { ne: EXAMPLE_PROJECT_ID } }));});
pub async fn read_todos_with_filters(jazz: &Jazz) -> jazz_app::Result<Vec<Todo>> {let query = todos::query().filter(todos::done().eq(false)).filter(todos::title().contains("docs"));jazz.all(query).await}
// 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?;
// 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?;
// Substring match (case-sensitive)let matches = jazz.all(todos::query().filter(todos::title().contains(search_term))).await?;
// 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?;
public func readOpenDocsTodos(_ jazz: Jazz.Client) async throws -> [JazzApp.Todo] {let todos = JazzApp.todos.self// Conditions passed to `where` are ANDed.return try await jazz.all(todos.query().where(todos.done.eq(false), todos.title.contains("docs")))}
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])))
// 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)))
// Substring match (case-sensitive).let matches = try await jazz.all(todos.query().where(todos.title.contains(searchTerm)))
// 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()))
// Conditions are ANDed.suspend fun readOpenDocsTodos(jazz: Jazz): List<Todo> =jazz.all(Todos.where(Todos.done eq false, Todos.title contains "docs"))
// 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)))
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))
// Substring match (case-sensitive)val matches = jazz.all(Todos.where(Todos.title contains searchTerm))
// 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()))
func ReadOpenDocsTodos(ctx context.Context, db *jazz.DB) ([]jazzapp.Todo, error) {// Conditions in one Where must all hold.return jazzapp.Todos.Query().Where(jazzapp.Todos.Done.Eq(false), jazzapp.Todos.Title.Contains("docs")).All(ctx, db)}
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)
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)
// Substring match (case-sensitive).matches, err := jazzapp.Todos.Query().Where(jazzapp.Todos.Title.Contains(searchTerm)).All(ctx, db)
// 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)
Sorting
Sort results with orderBy(...). Pass a column name and optionally "asc" or "desc".
export async function readTodosSortedByTitle(db: Db) {return db.all(app.todos.where({ done: false }).orderBy("title", "asc"));}
export const readTodosSortedByTitle = Effect.gen(function* () {const jazz = yield* Jazz;return yield* jazz.all(app.todos.where({ done: false }).orderBy("title", "asc"));});
pub async fn read_todos_sorted_by_title(jazz: &Jazz) -> jazz_app::Result<Vec<Todo>> {let query = todos::query().filter(todos::done().eq(false)).order_by(todos::title(), Order::Asc);jazz.all(query).await}
public func readOpenTodosByTitle(_ jazz: Jazz.Client) async throws -> [JazzApp.Todo] {let todos = JazzApp.todos.selfreturn try await jazz.all(todos.query().where(todos.done.eq(false)).orderBy(todos.title.asc))}
suspend fun readOpenTodosByTitle(jazz: Jazz): List<Todo> =jazz.all(Todos.where(Todos.done eq false).orderBy(Todos.title.asc()))
func ReadOpenTodosByTitle(ctx context.Context, db *jazz.DB) ([]jazzapp.Todo, error) {return jazzapp.Todos.Query().Where(jazzapp.Todos.Done.Eq(false)).OrderBy(jazzapp.Todos.Title.Asc()).All(ctx, db)}
Sorting by an enum
An enum column ({ "enum": [...] }) sorts its variants in the order your schema declares them, not
alphabetically. Reordering the variants, or adding one in the middle, changes the sort order
from that schema version on. Clients still on an older schema version sort by their own
declaration order. min, max and range filters such as gt follow the same order.
Pagination
limit(n) caps the number of rows returned; offset(n) skips that many rows. Combine them with a
deterministic order. If the first sort column is not unique, add orderBy("id") as the final
tie-breaker so page boundaries are stable.
export async function readTodoPage(db: Db, page: number, pageSize = 20) {const offset = Math.max(0, (page - 1) * pageSize);return db.all(app.todos.where({ done: false }).orderBy("title", "asc").orderBy("id", "asc").limit(pageSize).offset(offset),);}
export const readTodoPage = Effect.fn("readTodoPage")(function* (page: number, pageSize = 20) {const jazz = yield* Jazz;const offset = Math.max(0, (page - 1) * pageSize);return yield* jazz.all(app.todos.where({ done: false }).orderBy("title", "asc").orderBy("id", "asc").limit(pageSize).offset(offset),);});
pub async fn read_todo_page(jazz: &Jazz,page: usize,page_size: usize,) -> jazz_app::Result<Vec<Todo>> {let offset = page.saturating_sub(1) * page_size;let query = todos::query().filter(todos::done().eq(false)).order_by(todos::title(), Order::Asc).order_by(todos::id(), Order::Asc).limit(page_size).offset(offset);jazz.all(query).await}
public func readTodoPage(_ jazz: Jazz.Client, page: Int, pageSize: Int = 20) async throws-> [JazzApp.Todo]{let todos = JazzApp.todos.selfreturn try await jazz.all(todos.query().where(todos.done.eq(false)).orderBy(todos.title.asc, todos.id.asc).limit(pageSize).offset(max(0, (page - 1) * pageSize)))}
suspend fun readTodoPage(jazz: Jazz, page: Int, pageSize: Int = 20): List<Todo> {val offset = maxOf(0, (page - 1) * pageSize)return jazz.all(Todos.where(Todos.done eq false).orderBy(Todos.title.asc(), Todos.id.asc()).limit(pageSize).offset(offset),)}
func ReadTodoPage(ctx context.Context, db *jazz.DB, page, pageSize int) ([]jazzapp.Todo, error) {// Pages count from 1.offset := max(0, (page-1)*pageSize)return jazzapp.Todos.Query().Where(jazzapp.Todos.Done.Eq(false)).OrderBy(jazzapp.Todos.Title.Asc(), jazzapp.Todos.ID.Asc()).Limit(pageSize).Offset(offset).All(ctx, db)}
A deep offset still reads every skipped row: page 100 of 50 rows scans 5,050. For long lists, page
by a cursor instead, the last value of the sort column you showed:
const page = await db.all(app.messages.where({ sentAt: { lt: lastSentAt } }).orderBy("sentAt", "desc").limit(50),);
Each page then reads only the rows it returns. For now, also prefer a cursor on the backend: a large
offset with orderBy can fail on jazz.db.
Values over 64 KB
A string, bytes or json value over 64 KB is stored in chunks, and filters and sorting
never read those chunks. A text column with a text, markdown or html merge strategy
is chunked from 4 KB, so typing into a long document stays cheap; everything below applies
to its values over 4 KB. Shorter values filter and sort as described above. A value over
64 KB is compared by identity instead of by content:
- A filter literal over 64 KB in
eq,neorinis rejected with a "not supported yet" error, and a literal of 64 KB or less never equals such a value. - Comparing two columns, joining on them, or grouping by them only treats two such values as equal when they are the same stored value. Two equal values written separately, or one edited in place, may compare as different.
- Range comparisons (
gt,gte,lt,lte, where a query or policy offers them) andcontainsnever match it. - In ascending order it sorts before every shorter value, after rows where the column is null. It sorts in no meaningful order relative to other values over 64 KB.
Reading the value, in whole or in part, always returns its full content. If you need content equality, search, or ordering on long values, let us know: these are limits we plan to lift when someone needs them.