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 kindOperators / shape
ideq, ne, in
stringeq, ne, contains, in
booleaneq, ne, in
int / floateq, ne, gt, gte, lt, lte, in
timestampeq, ne, gt, gte, lt, lte, in
byteseq, ne, in
uuid (required)eq, ne, in
uuid?eq, ne, in, isNull
enumeq, ne, in
arrayeq, contains, in
jsoneq, 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 } }));
}

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"));
}

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),
);
}

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, ne or in is 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) and contains never 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.