Chain reference

Read Collections and chains first for the guide; come back here to look up one method. A trailing // value comment is what the line answers, and each example block stands alone: it creates its own collections.

Conventions

• A chain starts at a collection (db.orders, or db.collection("orders")) with one of its first calls, adds conditions, a sort and a window, and ends in a terminal. Nothing is read until a terminal runs.
• A chain is one object. A builder method adds to it and answers the same chain, not a copy: q.lt("n", 3) === q is true. Two starts at the collection are two chains.
• A chain can be kept and run many times. A value that varies between runs is db.arg(n), and the terminal takes the values as its arguments (q.all(10, "paid")): see Kept chains.
• A value is null, a boolean, a number, a string, a Date or db.arg(n); an array, an object or a BigInt throws a chain value must be null, a boolean, a number, a string, a Date or db.arg(n). eq, ne, in and nin also take binary (a Uint8Array, an ArrayBuffer or another view): it equals a stored binary of the same bytes, never a string of those bytes, and is a key lookup like any value; gt, between, startsWith and the rest throw for it. A run that supplies fewer values than the chain holds db.arg slots throws the chain takes 1 argument, got 0.
• A field name is a string, not empty. Where the table below says "path", a dotted path ("address.city") names a member of a document held in a field.
• Rows. all(), first(), last(), sample(), random(), each(), chunk(), page(), claim() and topPerGroup() answer records (instances of the collection class, with save(), set() and the rest) when the chain selects nothing. A chain that uses select, a join, groupBy, bucket, histogram, distinct() or a compound answers plain objects.
• Errors. An argument of a type the call does not take is a TypeError whose message names the call. Any other refusal is an Error whose name is its code: a value out of range or an unknown name INVALID_ARGUMENT, a form the chain does not run (never a slow walk instead) UNSUPPORTED_OPERATION, a missing collection COLLECTION_NOT_FOUND. A refusal worded by the query planner itself (an undeclared index, a plan it has no walk for) is still a TypeError. firstOrFail() throws NotFoundError. A chain method called with a this that is not a chain (taken off one and called on another value) throws a TypeError (chain: not a chain).
• ne, ge, le, sort_by are aliases of neq, gte, lte, sort.
• The guide covers what the reference does not repeat: nested paths, null and missing values, scopes, the declarations (unique, text, top, keyword, expire, elements), records and the write operators.

The table

"Starts" means the method can be the first call on a collection.

count, exists and top exist on a collection too, with another meaning (count(filter), exists(filter), and top(field, weight) declares a top-k field): as a collection's first call they are not chain methods.

Value conditions

eq(field, value) neq(field, value) ne

eq matches the documents whose field equals value; eq(field, null) matches those whose field is null or missing. neq matches the documents that hold a value other than value: a document without the field matches neither. A number equals a number, a string a string (1 is not "1"). Answers the chain. Throws TypeError: the operator takes a field and a value, a field name must be a string, a field name must not be empty. _id takes eq, neq, in and nin only.

gt(field, value) gte (ge) lt(field, value) lte (le)

Comparison of a number with a number, or a string with a string, in the type's own order: gt("v", 4) does not match a string "5". A document whose field is missing, null or of another kind does not match. Answers the chain.

in(field, values) nin(field, values)

in: the field equals one of the values. nin: the field holds a value, and none of these. An empty array matches nothing for in, every holder for nin. Answers the chain. Throws TypeError in/nin take a field and an array of values when the second argument is not an array.

between(field, low, high)

low <= field <= high, both bounds included; run as gte(low) and lte(high) of one field. A document with a null or missing field never matches. Answers the chain. Throws TypeError between takes a field, a low and a high bound, and between: a bound is null, which compares with nothing.

exists(field, present = true)

A condition: the field holds a value (present true), or is null or missing (present false). Answers the chain. With no argument, exists() is a terminal (see Reading terminals). On a bare collection exists(filter) takes a filter object and refuses a field name.

const Goods = db.collection("refgoods");
Goods.insertMany([
  { sku: "a1", price: 5, tag: "x", note: "Hello World" },
  { sku: "a2", price: 15, tag: "y" },
  { sku: "b1", price: 25, tag: "x", note: null },
  { sku: "B2", price: 35 },
]);
Goods.eq("sku", "a1").count();                       // 1
Goods.eq("note", null).count();                      // 3
Goods.neq("tag", "x").count();                       // 1
Goods.ne("tag", "x").count();                        // 1
Goods.gt("price", 5).count();                        // 3
Goods.gte("price", 5).count();                       // 4
Goods.ge("price", 15).count();                       // 3
Goods.lt("price", 25).count();                       // 2
Goods.lte("price", 25).count();                      // 3
Goods.le("price", 5).count();                        // 1
Goods.in("sku", ["a1", "B2"]).count();               // 2
Goods.nin("tag", ["x"]).count();                     // 1
Goods.between("price", 5, 15).count();               // 2
Goods.gt("price", 0).exists("note").count();         // 1
Goods.gt("price", 0).exists("note", false).count();  // 3
(() => { try { return Goods.between("price", null, 3); } catch (e) { return e.message; } })(); // "between: a bound is null, which compares with nothing"
(() => { try { return Goods.eq("sku"); } catch (e) { return e.message; } })(); // "the operator takes a field and a value"

Text conditions

startsWith(field, value)

The string field starts with value (case sensitive). Answered from the field's keys as one range. Answers the chain.

endsWith(field, text) contains(field, text) icontains(field, text)

The string field ends with, holds text. icontains compares ASCII letters without case. They read their candidates from the field's trigram keys and test each candidate exactly on its record. A text under 3 bytes has no key: beside another keyed condition it is tested on that condition's rows; alone it throws a TypeError (contains, icontains, endsWith, like and ilike need at least 3 characters ...), never a walk over every document. Throws endsWith, contains and icontains take a field and a string.

ieq(field, text) iin(field, texts) istartsWith(field, text)

Equality, membership and prefix with ASCII letters compared without case (É and é differ). A field that is not a string never matches. Answered from a folded key of the field. Throws ieq and istartsWith take a field and a string, iin takes a field and an array of strings.

like(field, pattern) ilike(field, pattern)

% any run of characters, _ any one character, a backslash makes the next character literal. like is case sensitive, ilike folds ASCII letters. abc% is startsWith, %abc endsWith, %abc% contains, abc eq; any other pattern is tested on each candidate behind the keys of its literal prefix or its longest literal segment. Throws like and ilike take a field and a string pattern.

regex(field, pattern)

The string field matches pattern somewhere. pattern is a string or a RegExp (ECMAScript grammar; only the i flag is read). Tested on each candidate: combine it with a keyed condition. Throws regex takes a field and a string or a RegExp, regex reads only the i flag of a RegExp, regex: the pattern is not a valid expression.

match(field, text, options?)

The documents whose declared text field holds every word of text ("all", the default) or at least one ("any"; with no sort they come ranked by distinct words matched). options is { mode } or the mode string. The field is declared with collection.text(field). Words are runs of letters, digits and _, ASCII case folded. Throws match: mode is 'all' or 'any' (INVALID_ARGUMENT), and a TypeError for a field that is not declared (match: 'f' is not a declared text field (collection.text('f'))). A chain with joins, distinct, a compound or another relational method refuses it.

const Hits = db.collection("refhits");
Hits.insertMany([
  { sku: "a1", note: "Hello World", body: "the quick brown fox" },
  { sku: "a2", note: "hello", body: "a quick red hen" },
  { sku: "B2", note: "WORLD", body: "slow green turtle" },
]);
Hits.text("body");
Hits.startsWith("sku", "a").count();                 // 2
Hits.endsWith("note", "World").count();              // 1
Hits.contains("note", "ell").count();                // 2
Hits.icontains("note", "world").count();             // 2
Hits.ieq("sku", "b2").count();                       // 1
Hits.iin("sku", ["A1", "b2"]).count();               // 2
Hits.istartsWith("sku", "A").count();                // 2
Hits.like("sku", "a_").count();                      // 2
Hits.ilike("sku", "b%").count();                     // 1
Hits.gt("sku", "").regex("note", /^h/i).count();     // 2
Hits.regex("note", "d$").count();                    // 1
Hits.match("body", "quick fox").count();             // 1
Hits.match("body", "fox hen", "any").count();        // 2
Hits.match("body", "fox hen", { mode: "any" }).count(); // 2

A field declared with collection.keyword(field) keys its words by their suffixes: contains and icontains of a word read those terms and test no record (explain().residual is empty).

const Notes = db.collection("refkw");
Notes.keyword("body");
Notes.insertMany([{ body: "alpha beta" }, { body: "gamma" }, { body: "Alphabet soup" }]);
Notes.icontains("body", "ALPHA").count();            // 2
Notes.contains("body", "beta").count();              // 1
Notes.icontains("body", "ALPHA").explain().residual; // []

Array conditions

An array condition reads a top-level field holding an array; a document whose field is not an array matches none of them.

containsAll(field, values) containsAny(field, values)

The array holds every, at least one of the values as an element (the same kind: 1 is not "1"). An empty list matches every array for containsAll and none for containsAny. Throws containsAll/containsAny take a field and an array of values, containsAll/containsAny take strings, numbers and booleans.

size(field, n) sizeGt(field, n) sizeLt(field, n)

The array has exactly, more than, fewer than n elements. Throws size, sizeGt and sizeLt take a field and a number.

elemMatch(field, filter)

The array of objects has an element satisfying every member of filter: a value is an equality, an object of $eq, $ne, $gt, $gte, $lt, $lte, $in, $nin, $exists, $startsWith, $endsWith, $contains, $icontains is one condition each. Throws elemMatch takes a field and a filter object (TypeError); an operator outside that list is INVALID_ARGUMENT (elemMatch: unknown operator $zz), an operand of a type the operator does not take a TypeError.

const Posts = db.collection("refposts");
Posts.insertMany([
  { n: 1, tags: ["js", "db", "perf"], lines: [{ sku: "a", qty: 1 }, { sku: "b", qty: 5 }] },
  { n: 2, tags: ["js"], lines: [{ sku: "a", qty: 3 }] },
  { n: 3, tags: [], lines: [] },
  { n: 4, tags: "js" },
]);
Posts.containsAll("tags", ["js", "db"]).pluck("n");               // [1]
Posts.containsAny("tags", ["perf", "js"]).pluck("n");             // [1, 2]
Posts.size("tags", 0).pluck("n");                                 // [3]
Posts.sizeGt("tags", 1).pluck("n");                               // [1]
Posts.sizeLt("tags", 2).pluck("n");                               // [2, 3]
Posts.elemMatch("lines", { sku: "a", qty: { $gte: 3 } }).pluck("n"); // [2]

Field and date conditions

eqField(a, b) neField(a, b) gtField(a, b) gteField(a, b) ltField(a, b) lteField(a, b)

Field a against field b of the same document; both fields of the chain's own collection hold a value (two numbers or two strings). Throws the operator takes two field names (TypeError); a joined field (alias.field) is UNSUPPORTED_OPERATION, a field comparison takes fields of the chain's own collection.

onDay(field, date, zone?) inMonth(field, year, month, zone?) lastDays(field, n, zone?)

The field holds milliseconds. onDay: the day of date (a Date or a number). inMonth: the month (1 to 12) of the year. lastDays: today and the n - 1 days before it up to now (n is a whole number from 1; now is taken when the chain runs). zone is the offset from UTC in minutes, east positive (at most 1440), UTC by default. Each becomes one range of the field's number keys. Throws onDay takes a field and a Date, inMonth takes a field, a year and a month (1 to 12), lastDays takes a field and a number of days (1 or more), the time zone is an offset from UTC in minutes.

const Prices = db.collection("refprices");
Prices.insertMany([{ n: 1, cost: 5, price: 9 }, { n: 2, cost: 8, price: 8 }, { n: 3, cost: 9, price: 4 }, { n: 4, cost: 1 }]);
Prices.gtField("price", "cost").pluck("n");                       // [1]
Prices.gteField("price", "cost").pluck("n");                      // [1, 2]
Prices.ltField("price", "cost").pluck("n");                       // [3]
Prices.lteField("price", "cost").pluck("n");                      // [2, 3]
Prices.eqField("price", "cost").pluck("n");                       // [2]
Prices.neField("price", "cost").pluck("n");                       // [1, 3]
const Events = db.collection("refevents");
const at = (d, h) => Date.UTC(2024, 2, d, h);
Events.insertMany([{ n: 1, at: at(5, 1) }, { n: 2, at: at(5, 23) }, { n: 3, at: at(6, 2) }, { n: 4, at: Date.UTC(2024, 3, 1) }]);
Events.onDay("at", new Date(at(5, 12))).pluck("n");               // [1, 2]
Events.onDay("at", new Date(at(6, 12)), 180).pluck("n");          // [2, 3]
Events.inMonth("at", 2024, 3).pluck("n");                         // [1, 2, 3]
Events.lastDays("at", 3).count();                                 // 0

Vector and geo conditions

A knn-family leaf ranks or filters by distance instead of matching exactly; combine it with the conditions above as any leaf. These are the chain forms of the filter object's $knn, $sparseKnn, $near, $geoWithin/$box/$sphere and $text (which have no filter object form of their own: see Filter objects). The vector, geo and text indexes themselves, and the HTTP $search, are covered in vectors-geo-rag.md.

knn(field, vector, k)

The k (an integer from 1) documents nearest field's vector to vector (a non-empty array of numbers), nearest first. Throws knn takes a field, a vector and a k from 1, knn takes a field, a non-empty array of numbers and a k from 1.

search(text, options?)

The chain's own vector mode of $search: embeds text with the collection's RAG provider and runs knn of the embedding against the RAG vector field, k documents (default 50) nearest first. options is { k, mode }; mode, when given, must be "vector" (the hybrid mode is the HTTP $search's). decay, boost_feedback and strict_model are options of the HTTP $search only; a chain refuses them. Throws search takes a text and an optional {k}, search: the collection has no RAG configuration to embed the text with, search: a chain answers the vector mode; the hybrid mode is the HTTP $search's.

near(field, lat, lng, k = 10)

The k (an integer from 1) documents nearest the point (lat, lng), nearest first. Throws near takes a field, a latitude, a longitude and a k from 1.

withinBox(field, low, high)

The documents whose field falls inside the box low-high (two non-empty arrays of numbers of one length: coordinates, or any bounded numeric vector). Throws withinBox takes a field and two arrays of numbers, withinBox takes a field and two non-empty arrays of numbers of one length.

withinSphere(field, center, radius)

The documents whose field falls inside the sphere of radius (a number above 0) around center (a non-empty array of numbers). Throws withinSphere takes a field, a center array and a radius above 0, withinSphere takes a field, a non-empty center array and a radius above 0.

sparseKnn(field, vector, k)

Sparse-vector knn: vector is { indices: [...], values: [...] } of one length (a sparse vector's nonzero positions and weights). Throws sparseKnn takes a field, {indices: [...], values: [...]} of one length and a k from 1.

textNear(field, text, fuzzy?)

Full-text relevance of text against field; fuzzy (a boolean or a number, off by default) allows near-matches. Throws textNear takes a field, a text and an optional fuzzy flag.

Groups

and(...fns) or(...fns) not(...fns)

Each fn is q => q.eq(field, value): it receives a chain and adds conditions to it. and: every function's conditions hold; or: at least one function's conditions hold; not: every document the conditions do not select (a document without the field included). A function may hold several conditions (they are ANDed) and nested groups. sort, limit, join and the other shaping methods are refused inside. Answers the chain. Throws and/or/not take functions: q => q.eq(field, value).

const Tasks = db.collection("reftasks");
Tasks.insertMany([{ n: 1, state: "open", p: 1 }, { n: 2, state: "done", p: 2 }, { n: 3, state: null, p: 3 }, { n: 4, p: 4 }]);
Tasks.and(q => q.gt("p", 1), q => q.lt("p", 4)).pluck("n");               // [2, 3]
Tasks.or(q => q.eq("state", "open"), q => q.eq("state", "done")).pluck("n"); // [1, 2]
Tasks.not(q => q.eq("state", "open")).pluck("n");                         // [2, 3, 4]
Tasks.or(q => q.eq("state", "open").lt("p", 5), q => q.gte("p", 4)).pluck("n"); // [1, 4]

Shaping

sort(field, direction?, options?) sort(keys) sort_by

direction: 1, "asc" or false ascending (the default); -1, "desc" or true descending. Call it again, or pass an array of names and [name, direction] pairs, for more keys: the first key's order, each run of equal values ordered by the next, ties in id order. Documents with no value (null or missing) come last in either direction. { nulls: "first" } puts them first (in id order), for a plain single-key sort of the collection's own field: it is refused with several keys, a join, a cursor page, last() and an aggregate. A field may be as.field of a join, and a path. Answers the chain. Throws sort direction is 'asc' or 'desc', sort's options (nulls) go with one key: sort(field, direction, { nulls }).

limit(n) skip(n)

The window of the sorted (or id-ordered) answer: integers from 0. count() counts before the window. Throws limit takes an integer from 0, skip takes an integer from 0.

select(...fields) select(fields)

The rows carry only these fields (_id only when named). A name with a dot that no join alias spells is a nested path: select("name", "address.city") answers { name, address: { city } }. Rows are plain objects. A path is refused with a join, groupBy, distinct, a compound or columns().

top(count, weight)

The count documents of greatest numeric weight, ties in row order; sets the chain's sort and limit and leaves out documents whose weight is not a number. After startsWith(field, prefix) on a field declared with collection.top(field, weight), a prefix of 1 to 3 bytes reads count keys. Not a first call on a collection (there top(field, weight) declares). Throws top takes a count from 1 and a weight field.

Where a condition is written against sort or top decides how it runs: one written before builds its row set, as every other condition does; one written after is tested on each record sort/top walks and builds none. Nums.startsWith("name", "a").top(5, "w") builds the prefix's row set; Nums.top(5, "w").startsWith("name", "a") does not, the walk's own test answers the prefix instead. Write the most selective condition first for the smallest row set, or last to skip building one at all when sort/top alone narrows enough.

const Nums = db.collection("refnums");
Nums.insertMany([1, 2, 3, 4, 5, 6, 7].map(n => ({ n, parity: n % 2 ? "odd" : "even", w: n * 10 })));
Nums.sort("n", -1).limit(3).pluck("n");                  // [7, 6, 5]
Nums.sort_by("n", "desc").limit(2).pluck("n");           // [7, 6]
Nums.sort("n").skip(2).limit(2).pluck("n");              // [3, 4]
Nums.sort("parity").sort("n", -1).pluck("n");            // [6, 4, 2, 7, 5, 3, 1]
Nums.sort([["parity", 1], ["n", -1]]).limit(2).pluck("n"); // [6, 4]
Nums.gt("n", 0).top(2, "w").pluck("n");                  // [7, 6]
Nums.sort("n").limit(3).count();                         // 7
Object.keys(Nums.sort("n").select("n").first());         // ["n"]
Nums.sort("n").select("n", "parity").limit(1).all();     // [{ n: 1, parity: "odd" }]

Relations

Relations are declared on the collection: hasMany(name, Target, key), hasOne(name, Target, key), belongsTo(name, Owner, key), belongsToMany(Target, { through, as, key?, otherKey? }).

with(...relations)

Each row carries the related records under the relation's name. "a.b" nests. with(name, q => ...) filters, sorts and limits the related rows of each parent. Rows stay records. Throws with: no relation 'x' is declared on 'c' (hasMany, hasOne, belongsTo or belongsToMany).

withCount(...relations)

Each row carries <name>Count, the number of related records of a hasMany relation, read from the count, with no record read.

whereHas(relation, constraint?) whereHas(relation, op, n) whereNone(relation, constraint?)

whereHas: the documents with a related record (matching constraint, a function q => q.gt(...), when given). whereNone: the documents with none. whereHas(relation, op, n) compares the number of related records (op: '>=', '>', '=', '<', '<='; n a whole number from 0; hasMany and hasOne): one count lookup per candidate document. It is refused with sort, inside and, or and not, and in a chain shape that reads the plan another way (a join, groups, columns). Throws whereHas takes a relation name, and optionally a function q => q.eq(...) or a comparison: ..., whereHas(relation, op, n) cannot be combined with sort: ....

Joins, subqueries and compounds

join(collection, localField, foreignField, as) innerJoin(...) leftJoin(...)

Adds the matching document of collection (the one whose foreignField equals the row's localField) as the row member as. join takes the first match (null when none). innerJoin and leftJoin give one row per match; innerJoin drops a row with no match, leftJoin keeps it with as null. A joined field is named as.field in conditions and sorts. SQL semantics apply (a null never compares equal). Rows are plain objects. A join cannot be combined with sample, random, each, chunk or a write. Throws join takes (collection, localField, foreignField, as), join: `as` must not be empty.

const Authors = db.collection("refauthors");
const Books = db.collection("refbooks");
const Reviews = db.collection("refreviews");
Authors.hasMany("books", Books, "authorId");
Books.hasMany("reviews", Reviews, "bookId");
Books.belongsTo("author", Authors, "authorId");
const ann = Authors.insert({ name: "Ann" })._id;
const bo = Authors.insert({ name: "Bo" })._id;
Authors.insert({ name: "Cy" });
const a1 = Books.insert({ authorId: ann, title: "A1", year: 2001 })._id;
Books.insert({ authorId: ann, title: "A2", year: 2010 });
Books.insert({ authorId: bo, title: "B1", year: 2005 });
Reviews.insert({ bookId: a1, stars: 5 });
Authors.sort("name").with("books").all().map(a => a.books.length);        // [2, 1, 0]
Authors.eq("name", "Ann").with("books", q => q.sort("year", -1).limit(1)).first().books[0].title; // "A2"
Authors.sort("name").withCount("books").all().map(a => a.booksCount);     // [2, 1, 0]
Authors.whereHas("books", q => q.gt("year", 2005)).pluck("name");         // ["Ann"]
Authors.whereHas("books", ">=", 2).pluck("name");                         // ["Ann"]
Authors.whereNone("books").pluck("name");                                 // ["Cy"]

The subquery and compound methods run with SQL semantics and answer plain rows. A compound's arms pair columns by position; sort, skip and limit go on the receiver, and an arm cannot itself be a compound or carry its own sort, skip or limit.

inQuery(field, chain, chainField) notInQuery(field, chain, chainField)

field is in (not in) the chainField values of another chain. The other chain is read as it is when the call is made. A null among the values makes notInQuery match nothing. Throws inQuery takes (field, chain, chainField).

existsQuery(chain)

Every document when the other chain has a document, none otherwise.

union(chain) unionAll(chain) intersect(chain) except(chain)

The rows of both chains' select() fields: union removes duplicates, unionAll keeps them, intersect the rows both have, except the rows this chain has and the other does not. Not first calls on a collection.

const Cust = db.collection("refcust");
const Ord = db.collection("reford");
Cust.insertMany([{ name: "ann", tier: "gold" }, { name: "bo", tier: "basic" }]);
Ord.insertMany([{ customer: "ann", total: 5 }, { customer: "ann", total: 7 }, { customer: "zed", total: 1 }]);
Ord.join("refcust", "customer", "name", "who").sort("total").all().map(o => o.who && o.who.tier); // [null, "gold", "gold"]
Ord.innerJoin("refcust", "customer", "name", "c").count();               // 2
Ord.leftJoin("refcust", "customer", "name", "c").count();                // 3
Ord.leftJoin("refcust", "customer", "name", "c").eq("c.tier", "gold").count(); // 2
Ord.inQuery("customer", Cust.eq("tier", "gold"), "name").count();        // 2
Ord.notInQuery("customer", Cust.eq("tier", "gold"), "name").count();     // 1
Ord.existsQuery(Cust.eq("tier", "gold")).count();                        // 3
Ord.existsQuery(Cust.eq("tier", "none")).count();                        // 0
Ord.select("customer").union(Cust.select("name")).sort("customer").all().map(r => r.customer); // ["ann", "bo", "zed"]
Ord.select("customer").unionAll(Cust.select("name")).all().length;       // 5
Ord.select("customer").intersect(Cust.select("name")).all().map(r => r.customer); // ["ann"]
Ord.select("customer").except(Cust.select("name")).all().map(r => r.customer);    // ["zed"]

Grouping

groupBy(...fields) groupBy(fields)

Groups the matching documents by the fields' values (as.field names a join's). The chain then takes count(alias?), sum(field, alias?), avg, min, max, having(...), sort, skip, limit, and all() answers one plain row per group: the group fields, then each aggregate under its alias (count, sum_<field>, avg_<field>, min_<field>, max_<field> by default). Groups come out in group key order unless sort names a key or an alias. An integer key from 2^53 is a BigInt. End the chain with all() or first(); groupBy('_id') is refused. Throws groupBy takes one or more field names, the aggregate takes a field and an optional alias, an aggregate alias must be a string, an aggregate alias must not be empty.

group_by(field)

The earlier form: a chain whose terminals sum(field), avg, min, max and count() answer one object with a property per group value (the value as JS spells it; "null" for a missing field). The group value "__proto__" is refused (group_by: a group value '__proto__' cannot key the result object).

having(field, op, value)

A condition over a group key or an aggregate alias; op is =, ==, !=, <>, >, >=, <, <= (or eq, ne, neq, gt, gte, ge, lt, lte, le). Several are ANDed. Not a first call on a collection. Throws having takes (field, operator, value), having's operator is =, !=, >, >=, < or <=.

bucket(field, step, options?) histogram(field, step, options?)

A group key that cuts the field's numbers (or dates, in milliseconds) into buckets of step: a number, or text such as "1h", "1d", "1w", "30m", "15s", "250ms". The group's value is the bucket's first value (floor(v / step) * step); rows with no number are one null group. { fill: true } also answers the empty buckets between the first and the last. bucket takes aggregates after it as groupBy does; histogram is bucket with count(): rows { field, count }. Throws bucket takes (field, step, options?).

countDistinct(field)

How many different values the field holds among the matching documents (null and missing do not count). A plain chain only. Throws countDistinct takes a plain chain: use groupBy(...).count() for groups.

topPerGroup(groupField, n, sortField, direction?)

The first n documents of each group of groupField in sortField's order (1, -1, "asc", "desc"), the groups in value order. Each group is an ordered key walk stopped at n rows. Documents with no group value are in no group. The chain holds conditions only (no sort, skip, limit or select). Throws topPerGroup takes (groupField, n, sortField, direction?), topPerGroup takes a chain with conditions only (no sort, skip, limit or select), topPerGroup's direction is 1, -1, "asc" or "desc".

distinct() distinct(field, options?)

distinct() after select(...): each row of the selected fields once, plain rows (the selected fields of the chain's own collection are answered by the aggregate stage; whole rows and joined fields keep SQL semantics). distinct(field, options?) is a terminal: the distinct values of the field among the matching documents, as an array (values() without the counts); options as for values.

const Sales = db.collection("refsales");
Sales.insertMany([
  { region: "north", amt: 10, at: 0 }, { region: "north", amt: 30, at: 3600000 },
  { region: "south", amt: 20, at: 10800000 }, { region: "south", amt: 5, at: 10801000 },
  { region: "south", amt: 40, at: 7200000 },
]);
Sales.groupBy("region").count().sum("amt", "total").sort("region").all(); // [{ region: "north", count: 2, total: 40 }, { region: "south", count: 3, total: 65 }]
Sales.groupBy("region").avg("amt").min("amt").max("amt").sort("region").first(); // { region: "north", avg_amt: 20, min_amt: 10, max_amt: 30 }
Sales.groupBy("region").count("n").having("n", ">=", 3).all();         // [{ region: "south", n: 3 }]
Sales.group_by("region").count();                                       // { north: 2, south: 3 }
Sales.group_by("region").sum("amt");                                    // { north: 40, south: 65 }
Sales.bucket("at", "1h").count().sort("at").all().length;               // 4
Sales.histogram("amt", 20).sort("amt").all();                           // [{ amt: 0, count: 2 }, { amt: 20, count: 2 }, { amt: 40, count: 1 }]
Sales.countDistinct("region");                                          // 2
Sales.topPerGroup("region", 1, "amt", "desc").map(r => r.amt);          // [30, 40]
Sales.distinct("region");                                               // ["north", "south"]
Sales.select("region").distinct().all().length;                         // 2

Reading terminals

A terminal runs the chain. Each takes the run arguments (db.arg values) as its last arguments.

all(...args)

The rows, in the chain's order: a record chain answers an Array of records with the result methods (first(), last(), ids(), pluck(), project(), saveAll(), deleteAll(), setAll(), incAll(): see records.md); a chain that selects, joins, groups or compounds answers an Array of plain objects. [] when nothing matches.

first(...args) last(...args)

The first, the last row in the chain's order, or null. last() of a sorted chain reads the sort's reversed walk (no walk of all rows); among rows that tie on the sort value it answers the lowest id. With no sort it is the last row in id order, read from the end of the collection. sort(..., { nulls: "first" }) is refused with last().

firstOrFail(...args)

first(), but a chain that matches nothing throws an Error named NotFoundError (firstOrFail: no document of 'c' matches).

value(field, ...args)

The field of the first row, or undefined when nothing matches or the row has no such field. Throws value takes a field name.

ids(...args) pluck(field, ...args)

The ids (strings) of the rows; one field of every row in the chain's order, built without a row (a missing field is undefined, a stored null is null, a path is read in place).

columns(...args)

An object with one array per selected field (_id and every field of the collection when nothing is selected; the system fields only when selected), in the chain's order. A column whose every cell is present and a number or boolean is a Float64Array (a boolean reads 0 or 1); any other is an Array of the cells' own values (strings, Dates, BigInts past 2^53), null where a document lacks the field. Not a first call on a collection. Throws columns() reads stored columns: a select of a nested path (a.b) answers rows, not columns.

count(...args)

How many documents match, before skip and limit. Not a first call on a collection (there count(filter)). After groupBy, bucket or histogram, count(alias?) is the group aggregate and answers the chain.

exists() missing()

Whether any document matches, and its negation. exists() with a field name is the condition above.

page(options, ...args) pages(options, ...args)

page({ size, after?, before?, total? }) answers { rows, next, prev, total? }: one page of the chain in its sort order (id order when unsorted). Pass next as after for the following page (null on the last page), prev as before for the page ahead (null on the first). A cursor is bound to the query. total: true adds the matching count. skip cannot be combined with a page, nor several sort keys, nor { nulls: "first" }. pages({ size }) is an iterator of row arrays, every page in turn. Throws page: a paged chain takes no skip (the cursor is its position), page: the cursor does not fit this chain's sort (the sorted field holds another kind of value now), pages: pages() steps forward; it takes size and after, page takes {size, after, before, total}, page: size must be an integer from 1, page: the cursor is not a cursor token.

const Items = db.collection("refitems");
Items.insertMany([
  { n: 1, g: "a", v: 10 }, { n: 2, g: "b", v: 20 }, { n: 3, g: "a", v: 30 }, { n: 4, g: "b" },
]);
Items.gt("n", 1).all().map(r => r.n);                      // [2, 3, 4]
Items.sort("n").first().n;                                 // 1
Items.sort("n").last().n;                                  // 4
Items.eq("g", "a").sort("n", -1).first().n;                // 3
Items.eq("g", "none").first();                             // null
Items.eq("n", 2).firstOrFail().v;                          // 20
(() => { try { return Items.eq("n", 9).firstOrFail(); } catch (e) { return e.name; } })(); // "NotFoundError"
Items.eq("n", 4).value("v");                               // undefined
Items.eq("n", 3).value("v");                               // 30
Items.eq("g", "a").ids().length;                           // 2
Items.sort("n").pluck("v");                                // [10, 20, 30, undefined]
Items.sort("n").select("n", "v").columns().v;              // [10, 20, 30, null]
Array.from(Items.sort("n").select("n").columns().n);       // [1, 2, 3, 4]
Items.sort("n").columns()._id.length;                      // 4
Items.sort("n").skip(1).limit(1).count();                  // 4
Items.eq("g", "b").exists();                               // true
Items.eq("g", "q").missing();                              // true
const first2 = Items.sort("n").page({ size: 2, total: true });
first2.rows.map(r => r.n);                                 // [1, 2]
first2.total;                                              // 4
first2.prev;                                               // null
Items.sort("n").page({ size: 2, after: first2.next }).rows.map(r => r.n); // [3, 4]
Array.from(Items.sort("n").pages({ size: 3 })).map(rows => rows.length); // [3, 1]

JSON terminals

json(...args) jsonFirst(...args)

The matching rows, or the first one, written straight to a JSON body: no row object is built per row. json() holds the chain's own [...] (or []), jsonFirst() one row (or null). The result is a JsonBody value: returning it from a function, or wrapping it in new Response(body, init), answers those exact bytes with no further copy. toString() reads the JSON text; .length is its byte length. A JsonBody is read once: a second read throws JsonBody already used. Not a chain with select, a join, with, groupBy, distinct or a compound: those answer another shape than one row per document, so json()/jsonFirst() throw naming the reason (json() and jsonFirst() do not support select yet). See collections-and-chains.md.

function (event) {
  return db.collection("refitems").sort("n").json();   // Response: 200, application/json, the array
}

Folds and key terminals

sort, skip and limit do not apply to these.

sum(field, ...args) avg(field, ...args) min(field, ...args) max(field, ...args)

The sum, mean, smallest and largest of the field's numbers over the matching documents. Only numbers count. sum of nothing is 0, the others null. Integers add exactly in int64 and the result is the nearest double (exact up to 2^53; past int64 it continues as a double and never wraps). min and max of keyed numbers read one key of the index. With a join or groupBy in the chain they are the group aggregates (see Grouping); on a relational chain without groupBy they throw sum, avg, min and max of a relational chain need groupBy.

values(field, options?, ...args) facets(fields, options?, ...args)

values: each value of the field among the matching documents with the number of documents holding it, { values: [{ value, count }], more }, read from the field's value keys where it has them. facets is values of several fields over one filter: an object with one { values, more } per field. options: top (the most values per field: 10 for facets, every value for values; 0 is every value, however many there are: no answer is too large, none is ever refused), order ("value": numbers, then strings in byte order, the default of values; "count": most first, ties by value, the default of facets), missing (adds one last { value: null, count } for the documents with no value), after (the answer starts past this value) and afterCount (that value's own count, for paging in count order: after and afterCount are given together and only together, and only with order: "count"; either alone, or with order: "value", throws). more is true when top cut the values.

const Scores = db.collection("refscores");
Scores.insertMany([
  { team: "red", pts: 10 }, { team: "red", pts: 20 },
  { team: "blue", pts: 5 }, { team: "blue", pts: 7 }, { team: "blue", pts: 9 }, { team: "blue" },
]);
Scores.sum("pts");                                   // 51
Scores.eq("team", "red").avg("pts");                 // 15
Scores.eq("team", "none").avg("pts");                // null
Scores.min("pts");                                   // 5
Scores.max("pts");                                   // 20
Scores.eq("team", "none").sum("pts");                // 0
Scores.values("team");                               // { values: [{ value: "blue", count: 4 }, { value: "red", count: 2 }], more: false }
Scores.values("team", { order: "count", top: 1 });   // { values: [{ value: "blue", count: 4 }], more: true }
Scores.values("pts", { missing: true }).values.length; // 6
Scores.facets(["team", "pts"], { top: 2 }).pts.more; // true
Scores.facets("team").team.values;                   // [{ value: "blue", count: 4 }, { value: "red", count: 2 }]

Walking terminals

sample(n, ...args) random(...args)

sample: n distinct documents of the answer picked at random, in the answer's order (all of them when fewer match; n an integer from 0). random: one document of the answer, or null. Throws sample takes a number of documents (an integer from 0). A grouped or joined chain is refused.

each(fn, ...args) chunk(n, fn, ...args)

each: fn(document, index) for each document of the answer in its order, stopping when fn returns false; answers how many documents fn was given. chunk: fn(documents, page) for each page of n documents (n an integer from 1). Both take the answer's ids first, so fn may write to the collection: a document removed meanwhile is skipped, one added meanwhile is not part of the walk. Throws chunk takes a page size (an integer from 1) and a function.

explain(...args)

Runs the chain once (rows are not built) and answers how it was answered: see How a chain runs.

const Deck = db.collection("refdeck");
Deck.insertMany(Array.from({ length: 20 }, (_, i) => ({ i, suit: i % 2 ? "red" : "black" })));
Deck.eq("suit", "red").sample(3).length;             // 3
Deck.eq("suit", "red").sample(100).length;           // 10
Deck.eq("suit", "red").random().suit;                // "red"
Deck.eq("suit", "none").random();                    // null
const seen = [];
Deck.sort("i").each((card, index) => { seen.push(card.i); return seen.length < 4; }); // 4
seen;                                                // [0, 1, 2, 3]
const sizes = [];
Deck.eq("suit", "black").sort("i").chunk(4, (cards, page) => { sizes.push(cards.length); }); // 10
sizes;                                               // [4, 4, 2]
Deck.eq("suit", "red").explain().route;              // "direct"

Writing terminals

A chain's writes belong to a chain, never to the bare collection (where update and delete take an id). The chain's conditions are the filter; its sort, skip and limit choose the rows as they do for ids(). They are refused after join, with, groupBy, distinct or a compound (update writes the chain's own documents: not after join, with, groupBy, distinct or a compound).

update(ops, ...args)

Applies ops (update operators, or plain fields to set) to every document the chain answers; answers the number written. A trailing { returning: true } answers the rows written (records, in order) instead. Throws update takes an object of update operators, or of fields to set.

delete(...args)

Deletes every document the chain answers; answers the number removed.

increment(field, n = 1, ...args) decrement(field, n = 1, ...args)

update({ $inc: { field: n } }) and update({ $inc: { field: -n } }): n a finite number or a BigInt inside int64. Answer the number written; a trailing { returning: true } answers the rows.

claim(n, ops, ...args)

The queue terminal: takes up to n (an integer from 1) documents the chain selects, in its sort order, applies ops ($set, $inc, $mul, $min, $max, $unset) to each, guarded by the chain's own conditions so two claimers never claim one document. Answers the claimed rows, after the update, in claim order; [] when none. Refused: a join, select, groupBy, skip, limit, and a condition a guard cannot re-test (or, not, in, between, like, ...). Throws claim: the second argument is the update, such as {$set: {state: 'running'}}, claim: a claiming chain takes no skip or limit (n is the limit), claim: the update changes no field.

const Jobs = db.collection("refjobs");
Jobs.insertMany([
  { job: 1, state: "ready", tries: 0, at: 3 }, { job: 2, state: "ready", tries: 0, at: 1 },
  { job: 3, state: "done", tries: 1, at: 2 }, { job: 4, state: "ready", tries: 0, at: 4 },
]);
Jobs.eq("state", "ready").sort("at").claim(2, { $set: { state: "running" }, $inc: { tries: 1 } }).map(r => r.job); // [2, 1]
Jobs.eq("state", "running").count();                 // 2
Jobs.eq("state", "none").claim(1, { $set: { state: "running" } });   // []
Jobs.eq("state", "ready").update({ $set: { hot: true } });          // 1
Jobs.eq("hot", true).update({ $set: { hot: false } }, { returning: true }).map(r => r.job); // [4]
Jobs.eq("state", "running").increment("tries");      // 2
Jobs.eq("state", "running").decrement("tries", 2);   // 2
Jobs.sort("job").pluck("tries");                     // [0, 0, 1, 0]
Jobs.eq("state", "done").delete();                   // 1
Jobs.count();                                        // 3

Kept chains

db.arg(n) is the value of the run's nth argument (n an integer from 0). A chain holding arguments is built once; each terminal call supplies the values, which are typed when they bind. Throws db.arg(n): n is an argument's position, an integer from 0.

const Pts = db.collection("refpts");
Pts.insertMany([{ n: 5 }, { n: 50 }, { n: 500 }]);
const big = Pts.gt("n", db.arg(0));
big.count(10);                                       // 2
big.count(100);                                      // 1
big.all(100).map(r => r.n);                          // [500]

in and nin also take db.arg(n) bound to an array: the run passes the array in place of a literal one, and the leaf answers as it would for that same array written in the chain.

const Many = db.collection("refmanypts");
Many.insertMany([{ n: 5 }, { n: 50 }, { n: 500 }]);
const kept = Many.in("n", db.arg(0));
kept.count([50, 500]);                               // 2

Filter objects

find, findOne, count, exists, updateWhere, deleteWhere, findOneAndUpdate and the other filter forms take one filter object. It is read into the same plan a chain builds, so both forms answer alike, and a filter the chain has no plan for throws an Error that says why (its name the code, as below): no other engine answers it.

A member holds a value (an equality) or an object of operators. Beside the members, $and, $or and $nor take non-empty arrays of filter objects (nested to any depth the call can hold). A missing, null or undefined filter is every document.

The operators $text, $fuzzy, $search, $knn, $sparseKnn, $near, $geoWithin, $box, $sphere and $whereJS have no filter form: they are refused by name ($search, $whereJS and a search operator on _id UNSUPPORTED_OPERATION, a top-level $knn or $box INVALID_ARGUMENT, an operand of the wrong type a TypeError). null is the operand of $eq, $ne, $in and $nin only; a boolean of $eq, $ne, $in, $nin, $exists and $all only; _id takes $eq, $ne, $in and $nin over ids, never null (UNSUPPORTED_OPERATION); $all, $size and $elemMatch read a top-level field, not a path. Refused with a TypeError: undefined, a BigInt, a RegExp object (write { $regex, $options: "i" }), an integer from 2^53, an array as the value of a field (use $in, $all, $size). Refused with INVALID_ARGUMENT: an empty operator object, an unknown operator, an empty $all, $nor, $and or $elemMatch.

The options of find: sort ({ field: 1 | -1 } or an array of names and [name, 1 | -1] pairs, never _id), limit and skip (integers from 0; limit: 0 answers empty), select (an array of top-level names; plain rows), after and before (cursor strings or null; a paged find takes one sort key and limit). Any other option throws the options are limit, skip, sort, select, after and before.

const Shop = db.collection("refshop");
Shop.insertMany([
  { sku: "a", shelf: 1, qty: 1, tags: ["new", "hot"], lines: [{ p: "x", n: 2 }] },
  { sku: "b", shelf: 2, qty: 5, tags: ["old"], lines: [] },
  { sku: "c", shelf: 1, qty: 9, tags: [] },
]);
Shop.find({ qty: { $gte: 5 } }).length;                            // 2
Shop.find({ sku: { $in: ["a", "c"] }, shelf: 1 }).length;          // 2
Shop.find({ sku: { $regex: "^B", $options: "i" } }).length;        // 1
Shop.find({ sku: { $not: { $in: ["a", "b"] } } }).length;          // 1
Shop.find({ $or: [{ sku: "a" }, { qty: { $gt: 8 } }] }).length;    // 2
Shop.find({ $nor: [{ sku: "a" }, { qty: { $lt: 5 } }] }).length;   // 2
Shop.count({ tags: { $all: ["new", "hot"] } });                    // 1
Shop.count({ tags: { $size: 1 } });                                // 1
Shop.count({ lines: { $elemMatch: { p: "x", n: { $gte: 2 } } } }); // 1
Shop.find({}, { sort: { qty: -1 }, select: ["sku"] }).map(r => r.sku); // ["c", "b", "a"]
(() => { try { return Shop.find({ qty: { $near: [1, 2] } }); } catch (e) { return e.name; } })(); // "TypeError"

How a chain runs

Building a chain only records it: each method adds a typed condition, sort key or window to the chain's plan (a field is a typed id, an operator an enum, a value a typed argument). A terminal binds the values and hands the plan to one of three routes, and explain() says which one answered.

const Logs = db.collection("reflogs");
Logs.insertMany([{ n: 1, level: "warn", tags: ["a"] }, { n: 2, level: "info", tags: [] }, { n: 3, level: "warn", tags: ["b"] }]);
const plan = Logs.eq("level", "warn").containsAll("tags", ["a"]).explain();
plan.route;                                                       // "direct"
plan.conditions.map(c => c.op);                                   // ["eq", "containsAll"]
plan.residual.map(c => c.op);                                     // ["containsAll"]
Logs.ne("level", "warn").explain().route;                         // "direct"
Logs.ne("level", "warn").explain().driver;                        // { field: "level", kind: "keyedSets" }
Logs.sort("n", -1).limit(1).explain().driver;                     // { field: "n", kind: "sortWalk" }
Logs.groupBy("level").count().explain().aggregate;                // "keys"

The direct route (route: "direct") reads value keys. A field's keys are ordered entries of its values, each pointing at a document, kept by every write. A condition the keys answer exactly is proven by them: no record is read to test it. Equality and in read the value's posting list, a range or startsWith one range of the ordered keys, eq(f, null) and exists(f, false) the field's absent keys, ieq, iin and istartsWith a folded key of the field, a sort the sort field's keys in order (the sort walk), and a count of one equality the keys' counters (no row read). Some conditions use keys only to name candidates, which are then tested on their records: containsAll and containsAny (the field's element keys), endsWith, contains, icontains and like (the trigram keys of the text).

The columns route (route: "columns") runs over a columnar copy of the collection. It answers what the direct route declined, and declined says why.

The relational route (route: "relational") is the SQL engine: compounds, subqueries, whole-row distinct(), and the joins, groups and having the direct route declines. Its results have SQL semantics (a null never compares equal), and it refuses the document conditions (the array, contains-family, regex, ieq-family, field-comparison and search methods) with chain over 'c' has no SQL form: a document condition ... which only a chain that is not relational runs.

Keys come from use. _id is always a direct lookup, and a field declared with unique, index.create or keyword is keyed before the declaration answers. Any other field is keyed by the first query that filters, sorts or groups on it: a field's keys move from none to building to ready; a query that needs a field's keys builds them in that call, from the documents already stored, and runs again from them; after that every insert, update and delete keeps them. The first such query pays for one pass over the collection's documents. Trigram, folded and absent keys are built the same way, by the first query that needs them. The word keys of a field declared with text are built once; a ranked match that finds them still building waits for them, up to the call's deadline (then it throws, and does not run). A chain can therefore be slower the first time it names a field than every time after.

Records are read once. The direct route reads a record only for what the keys did not prove, and reads each candidate record once per statement: the conditions the keys cannot answer (the residual) are tested on it, and the same read supplies the sort value, the selected fields (select, pluck, columns, read by slot) or a fold's number. A fold (sum, avg, min, max) over keyed numbers reads keys (min and max one key); a grouped count of one field with no condition walks that field's keys, one count per value, and reads no record (explain().aggregate is "keys"); otherwise each matched record is read once ("rows").

explain() answers a plain object:

explain() does not report how many records were read.

What makes a query cost more

• A condition that is in residual costs one record read per candidate the keys leave. The conditions with no key that proves them are size, sizeGt, sizeLt, regex, eqField and the other field comparisons, elemMatch (its candidates come from an elements declaration when there is one), the confirmations of containsAll, containsAny and the trigram-keyed text leaves, and a like pattern beyond its literal prefix. Put a selective keyed condition (eq, in, a range, startsWith) beside them.
• A chain whose conditions have no keyed leaf is still answered by the direct route: ne, nin and exists(true) by arithmetic over the field's keys (driver.kind "keyedSets", nothing in residual), every other leaf (regex, size, a field comparison) by one pass over the documents that reads each record once (driver is null and the condition is in residual). The columns route answers only what the direct route declines.
• A sort is a walk of the sort field's keys; a sorted run whose filter has no keyed condition declines to the columns route (SortFilterUnkeyed). limit stops the walk, so a limited sorted read costs its window.
• The first query that names a field builds its keys (one pass over the documents), as above.
• whereHas(relation, op, n) and withCount read one count per owner and no related record; with reads the related records of each row it returns.
• A compound, a subquery, a whole-row distinct() or a grouping the aggregate stage does not cover runs through the SQL engine.
• ids(), count(), exists() and pluck() build no row; all() builds a record per row; columns() builds one array per field.