<!-- GENERATED FILE — do not edit; regenerate with `./do v2-docs` (from core/). -->

v2 Query Operator Reference (Beta)

The complete operator vocabulary of the v2 (Query2) JSON query language — where conditions, orderBy, select, query types, and config. v2 is in beta; see the v2 Introduction for context and the v1 reference for the production API.

A query is one JSON object. The same from/where is evidence for a plain query, a prediction, a recommendation, or relation analysis — see the Inference guide.

Where conditions

where is an object of field → condition. A bare value means equality:

{ "from": "products", "where": { "category": "books" } }

Equality and tokens

A bare value on a Text column: filter or evidence

Filtering and inference read a Text value differently, and a where means whichever the query does:

• In a filter — a query that only selects rows (_query/_search, with no ordering or ordered by a stored field) — { "name": "BASF Oy" } returns the rows whose name is BASF Oy, character for character. Not "BASF Construction Chemicals Finland Oy", not "Oy BASF", not "basf oy". This holds through a link ({ "vendor.name": "BASF Oy" }) and inside $or/$not, and it is what v1 on a type: "table" has always answered. For the old token match, write $match.
• As evidence — the where of a _predict, _recommend, _match, or a query ordered by $p/$lift/$similarity — the value is its tokens, so a description matches by its words and a new wording of a known name still finds it.

$has is not a substitute for equality on a Text column: it is token membership, so { "$has": "BASF Oy" } for a name that is not stored matches rows by their tokens. Use the bare value.

On a member-link's linked Text attribute (items.name where products.name is Text), $has matches by token — { "items.name": { "$has": "banana" } } finds baskets with a product named "pirkka banana" or "chiquita banana" (the shared token, not the whole phrase). select ["items.name"] still returns the whole names.

Text search

Text columns (type: "Text", with an analyzer) support three matchers:

$match is the simple "all words present" matcher; $search is the expressive one when you need phrases, OR, or negation.

Ranges and prefixes

$numeric matches a neighbourhood: Aito bins the column adaptively, so $numeric: 101 draws on nearby values instead of overfitting the single exact row — the mechanism that makes a number useful as evidence. A $gt on a non-numeric column fails loud.

Date & timestamp components

Date and Timestamp columns are queried by derived component. A Timestamp (a UTC instant) additionally exposes the sub-day components, computed from the instant at query time — raw units, so you bucket them how you like:

{ "from": "orders", "where": { "created": { "$weekday": 6 } } }
{ "from": "events", "where": { "ts": { "$hour": 9, "$quarter": 3 } } }

Nullability

Fields through a link

Every operator above also applies to a field reached through a link by its dotted path — { "part.price": { "$gte": 10 } } filters orders by the linked part's price, exactly as { "price": { "$gte": 10 } } would filter parts. The operator vocabulary is a property of the field's type, not of how you reached it: a bare value against a linked Set/Array column is membership ({ "part.tags": "steel" } ≡ { "part.tags": { "$has": "steel" } }, as on the column itself), and a column value operation selects through the link too ("select": ["part.desc.$tokenCount"] is the parts column's count).

When the link itself is null (a nullable link with no target on that row), the path has no value on that row: no operator matches it, $not does not select it, select projects it as null, and { "<link>.<field>": { "$exists": false } } is how you select those rows — the same rules as a nullable column read directly.

Logical combinators

Combinators work on both collection tables and legacy table worlds.

Empty clause lists. A client that builds a filter list — { "id": { "$and": cart.map(...) } } — sends an empty list whenever there is nothing to filter on. Both are accepted, and each means what it says. An empty $and is a conjunction of no conditions, so it constrains nothing: it is ignored and the rest of the where still applies. An empty $or/$in is a disjunction with no branch that can match, so it selects nothing — zero hits, not every row. If you meant "no filter", omit the term rather than sending an empty $or.

Reference a stored row — $examine

Condition a prediction on the attributes of a linked entity you only hold a reference to: Aito looks the row up and expands the named attributes into evidence (equivalent to writing link.attr: <value> by hand). It works even for an entity with no rows of its own — the prediction generalizes through the attributes, not the unseen id. Requires basedOn; collections only.

Vector similarity

$nearest ranks rows by similarity to a query vector on a Vector column. It is a single where proposition that produces its own ordered, scored result set — the nearest rows first, each carrying its score as $similarity.

{ "from": "products",
  "where": { "$nearest": {
    "near":  { "embedding": [0.12, -0.03, 0.88] },
    "where": { "category": "shoes" },
    "having":{ "$similarity": { "$gte": 0.8 } },
    "limit": 10 } },
  "select": ["id", "name", "$similarity"] }

$similarity is the per-row score, exposed as a selectable field; rows come back ordered by it descending (an implicit orderBy $similarity). Its meaning follows the column's similarity setting: for cosine it is the cosine similarity in [-1, 1] (vectors are unit-normalised on ingest, so scoring is an exact dot product); for dotProduct it is the raw inner product. Rows whose vector is null are skipped, not scored.

$nearest is exact nearest-neighbour (a full scan of the candidates), not an approximate index — results are exact and reproducible, with ties broken by row order.

Vectors as prediction evidence — $cluster / $semantic

Beyond search, a Vector column can drive prediction. On a predict query, two operators turn similarity into evidence.

$cluster quantizes a vector into a categorical code (k-means), so it becomes an ordinary discrete feature the model reasons over:

{ "from": "tickets", "predict": "category",
  "where": { "embedding": { "$cluster": { "vector": [0.1, 0.9, 0.2], "k": 64 } } } }

The vector is coded against the column's codebook and the condition rewrites to a plain equality on the code column — so the vector becomes ordinary categorical evidence. A single full-vector cluster recovers most of the exact-kNN lift.

$semantic blends the k nearest neighbours' predict-field values into the prediction — semantic-kNN late fusion, the continuous-similarity form:

{ "from": "tickets", "predict": "category",
  "where": { "embedding": { "$semantic": { "near": [0.1, 0.9, 0.2], "k": 20, "weight": 0.5 } } } }

The k nearest rows vote their predict field, similarity-weighted, and the vote is fused with the ordinary prediction. $knn/$nn do the same with feature similarity instead of vector similarity (see below).

Text introspection

On a text field you can also read its analysis: $size (row count), $tokenCount (distinct tokens), $distinctCount (distinct values). Select them with a field.$op token — e.g. "select": ["note.$tokenCount"]. The value is a property of the whole column, so every returned row carries the same number (a per-row constant); pair it with "limit": 1 to read it once.

Unusual values (experimental)

Only an x-knowledge column answers $surprisal; on any other column it is refused by name. It needs one learned grammar, so on a collection written in several batches it is refused until the collection is optimized. Higher N selects fewer, stranger values — a useful N depends on the column, so start high and lower it.

Inverse links — $refs

A dotted path follows a forward link — from a row to the row it points at (order.buyer resolves the order's user, and buyer.name reads that user's name). $refs follows a link the other way: from a row to the rows that point at it. $refs.<table>.<fk> is the set of <table> rows whose link <fk> targets this row — e.g. on a user, $refs.orders.buyer is that user's orders (the orders whose buyer is this user). Append an attribute to project it over that set: $refs.orders.buyer.product is the products of those orders.

It is an ordinary field — you query, select, and predict over it with the same operators a stored member-set uses. The query is identical whether the relation is stored on the row or discovered via the back-link; nothing is copied onto the referring row.

Link resolution is lazy, and a link's cross-table enrichment is optional. A link's target is resolved at query time, not validated when the schema is created — so you may declare a link before its target table exists, and a link whose target is missing, renamed, or deleted does not raise an error. Its linked columns and its cross-table prediction signal are simply absent for that query, and the result degrades gracefully to what the row itself carries. (This is the long-standing rep1 behaviour, preserved by design.) Cross-table priors additionally require the target to be a rep2 collection; a link to a rep1 (type: "table") target resolves as absent the same way. So if a predict seems to ignore a linked signal, confirm the target table exists and is a rep2 collection.

Where — over the referring set:

Row-level $and of two $refs conditions intersects, e.g. a user with both a gold order and a web order:

{ "from": "users",
  "where": { "$and": [
    { "$refs.orders.buyer.product": { "$has": "gold" } },
    { "$refs.orders.buyer.channel": { "$has": "web" } } ] } }

Select — project the attribute over each row's referring set (an array per row):

{ "from": "users", "select": ["name", { "products": "$refs.orders.buyer.product" }] }
// → { "name": "Alice", "products": ["gold", "silver"] }, …

Predict — use a $refs condition as where evidence to predict a field from the back-linked rows, without materialising anything onto the row:

{ "from": "users",
  "where": { "$refs.orders.buyer.product": { "$has": "gold" } },
  "predict": "segment" }

A same-member conjunction — one referring row that matches several attributes at once (an order that is gold and on web) — is the object form: { "$refs.orders.buyer": { "$exists": { "product": "gold", "channel": "web" } } }. Contrast the row-level $and above, which matches any gold order and any (possibly different) web order.

Ordering — orderBy

{ "from": "impressions", "get": "product",
  "orderBy": { "$p": { "$context": { "purchase": true } } },
  "select": ["$value", "$p"] }

Projection — select

Plain column names, computed value-expression columns (arithmetic over fields — see Derived fields → let), plus the predictive columns below. On a get over a link, the link TARGET's columns are selectable too — each candidate resolves them on its target row (get: "product" + select: ["name", "price"] returns each candidate product's own attributes):

A ranked-candidate query also accepts a top-level having filter on the per-candidate frequency: "having": { "$f": { "$gte": 1 } } keeps only candidate values that actually co-occur with the where. This is the server-side tenant-scoping idiom — where supplies evidence, not an output domain, so a tenant-scoped predict otherwise ranks every tenant's values with the foreign ones in the tail at $f: 0. Applied after ranking (no renormalisation), before offset/limit; only $f with one of $gte/$gt/$lte/$lt is accepted, anything else is a loud 400. | $f | The candidate's frequency — count of rows where the predicted field equals this value, under the where | | $lift | The candidate's lift — how much the where evidence raises its probability over its base rate | | $why | The explanation factor tree (see the Inference guide). The parameterized form { "$why": { "highlight": { "posPreTag": "<b>", "posPostTag": "</b>" } } } additionally marks the matched evidence tokens in the explanation with your tags | | $highlight | On a search query: the matched field value with the query's tokens marked with tags — the search-hit form of $why's highlight. On a predict/recommend of a link field: the candidate's OWN attribute values with the evidence-carrying parts marked — an attribute whose prior lift is above 1 gets the positive tags, below 1 the negative tags (see the Inference guide). The parametric form { "$highlight": { "posPreTag": "<mark>", "posPostTag": "</mark>", "negPreTag": …, "negPostTag": …, "encoder": … } } sets the markup (SQL: startSel / stopSel on search(..)) | | $matches | On a search query: the matched tokens and their positions in the field value | | { alias: "source" } | Rename a column in the output — { "demandScore": "$score" } | | { alias: <value-expression> } | A computed column — { "margin": { "$subtract": ["revenue", "cost"] } }. Any let value-expression (arithmetic over fields/constants; see Derived fields); evaluated the same way as let, so select and let share one vocabulary | | { alias: { "$p": { "$context": <where> } } } | A named per-candidate contextual probability column | | { "$sum" \| "$mean": { "$context": <field> } } | Per-candidate aggregate of a context-table field over the candidate's where-filtered rows (bare, or wrapped in an alias) — e.g. a per-product conversion rate { "$mean": { "$context": "purchase" } } | | $count / $sum:f / $avg:f / $min:f / $max:f | Relational aggregate over the whole where selection — collapses all matching rows to ONE hit (SQL count(*), sum(f), …), ignoring offset/limit. Skips null cells; null over zero rows. Distinct from the per-candidate { "$sum": { "$context": … } } above; mixing an aggregate with a plain field is rejected (SQL would need a GROUP BY) | | field.$predictions | A per-row predicted column: for each selected SOURCE row, the ranked distribution [ { "value": …, "p": … }, … ] over the field — computed from the row's OTHER fields, with the predicted field held out. Collections only; default top-N. (No leave-one-out; use _evaluate for an unbiased estimate) | | field.$prediction | The argmax of the above — the single most likely value | | { alias: { "$predict": "field", "limit": n, "basedOn": [ … ] } } | Object form of a predicted column — cap the distribution (limit) and/or generalize a predicted link's candidates by their attributes (basedOn) |

Selecting an unsupported computed column fails loud (a 4xx), never a silent empty value. Aggregates and $f require candidates whose rows live in the from table itself — a dotted-path get (e.g. get: "context.week") is a known limitation (returns zeros; tracked on the gap matrix).

Derived fields — let

let defines inline computed fields from value-expressions, usable in select and orderBy. It is the join-free way to compute derived columns — margin, ratios, and the like — without a CTE or self-join:

{ "from": "sales",
  "let": { "margin":    { "$subtract": ["revenue", "cost"] },
           "marginPct": { "$divide": [ { "$subtract": ["revenue", "cost"] }, "revenue" ] } },
  "select": ["id", "margin", "marginPct"], "orderBy": "margin" }

A value-expression is one of:

Parts nest arbitrarily. A missing or non-numeric part, or division by zero, makes that row's value undefined.

from — collections, views, and inline relations (Beta)

from names what a query reads. Besides a plain collection it accepts a view (a derived relation saved in the schema) or an inline relation expression (an ad-hoc join or union for a single query). All are queried, filtered, projected, and predicted exactly like a collection — a view is just the saved, materialised form of the same relation operators.

Views are created and refreshed in the schema — see Schema Design → Views for the full treatment (both kinds, worked examples, _refresh semantics), and Relationships, Links & Joins for how link-joins behave in a query. A view is a materialised snapshot — rebuild it with POST /api/v2/schema/{view}/_refresh after its sources change; an inline $text union is tokenised per query, so for a hot cross-collection search prefer a persisted union view (built once).

Query types (top level)

Endpoints: /api/v2/_query is the unified entry; /_predict, /_search, /_recommend, /_relate are aliases that take the same body; /_batch runs a JSON array of queries. Also on v2, engine-dispatched (collections run natively; rep1 tables reuse the v1 pipeline):

Config

Per-query scoring options — the config.ai object form

config.ai accepts either a preset name or an object. The object form selects the same preset (preset) and, in addition, overrides individual scoring options for that one request:

{ "from": "invoices", "predict": "Processor",
  "where": { "Description": "monthly cloud hosting" },
  "config": { "ai": { "preset": "v2", "nameBoost": false, "mediationT": 2.0 } } }

Options are resolved in layers — built-in defaults, then the deployment's ops settings, then this query — so an object naming one option changes exactly that option and leaves the rest at their defaults. Nothing is global: two concurrent requests can score with different options without affecting each other. Unknown keys, unknown values and out-of-range numbers are rejected with a 400 naming the option (never silently ignored). A query with no config.ai is scored exactly as before.

The three cost guards (mediationMaxCandidates, nameBoostMaxCandidates, stage4MaxClasses) bound work that grows quadratically with the candidate count; the memory guard (stage4ClassIndexMaxRows) bounds a transient per-state index, and hitRelAccuracy bounds how much of the relation-discovery pool is upgraded from sampled to exact statistics. All four behave the same way: a query may LOWER one — making itself cheaper — but a request to raise it above the deployment's configured ceiling is rejected with a 400 rather than silently clamped.

The same object form is accepted by /_evaluate (evaluate.config.ai), so an A/B of two scoring configurations is two measured runs, not a restart.

Feature-based nearest rows — $knn / $nn

Rank rows by feature similarity to an exemplar (text fields score by BM25). This is a where operator (the feature analog of the vector $nearest / $semantic above), not a config setting:

{ "from": "msgs", "where": { "$knn": { "near": { "text": "sim card" } } }, "limit": 5 }
{ "from": "msgs", "where": { "$nn": { "near": { "text": "sim card" }, "threshold": 0.5 } } }

$nn keeps only rows scoring above threshold (default 0.0). The v1 array form { "$nn": [ { "text": "…" } ] } is accepted with a single exemplar. $similarity is selectable on the results.

On a predict query, $knn/$nn become feature-kNN classification — the feature analog of $semantic: the k feature-nearest neighbours vote the predict field, blended with the ordinary prediction.

{ "from": "tickets", "predict": "agent",
  "where": { "$knn": { "near": { "text": "password reset" }, "k": 2, "weight": 0.5 } } }

k (neighbours to vote, default 10) and weight (blend in [0, 1], default 0.5) apply only in this predict context; as a plain search operator $knn/$nn page by the query's limit.

Population restriction — nested from

from accepts a restriction object instead of a table name:

{ "from": { "from": "invoices", "where": { "customer_id": "CUST-0000" }, "limit": 50000 },
  "where": { "gl_code": "1600" },
  "predict": "approver" }

Rows failing the inner where are masked out of the examined table — the population the query sees. This is different from adding the filter to the outer where: where is evidence (it conditions the prediction and contributes lift), while the nested from restricts the row population, so base rates, support counts and candidates all compute over the subset (the multi-tenancy idiom). The optional inner limit caps the restricted population to its first N rows. One restriction level; a restriction matching no rows, deeper nesting, and unsupported combinations (relate, bare search, $nearest/$knn, config.calibrate) all fail loud.

One entity's properties — relate: {"$props": …}

The field-array form ranks EVERY value of the listed fields against the where. When the question is about one entity — "for each property value of THIS product, what does it do to purchase?" — name the propositions instead:

{ "from": "impressions",
  "where": { "purchase": true },
  "relate": { "$props": { "product.category": "100", "product.name": "Pirkka banana" } } }

Each named proposition becomes one hit, related to the where. The statistics are the field-array form's: scoping decides which relations come back, never their values. It also lifts the enumeration's ranking cap, so a value that would sit past the top of a population-wide ranking is still returned — the reason this is a form rather than client-side filtering.

v1's nested-entity spelling {"product": {"category": "100"}} is accepted as the same request, flattened onto the link paths (product.category). A FLAT relate object keeps its own meaning (relate every field to that condition); an object mixing the two is refused. $props takes plain field-value propositions — a combinator ($or, $not) names a set, not a relation, and is refused; so is a value no row carries. On a Text column a named proposition is the WHOLE value — "Pirkka banana", not "banana" — because that is the proposition a filter takes; name product.name.$token to relate one word instead (next section).

Which index a text column relates — .$distinct / .$token

A text column has TWO indexes: its distinct whole VALUES and its analyzed TOKENS. relate, $patterns and $props can name either, by suffixing the field:

Asking a column for an index it does not have is refused by name ($token on a String column names the column and its type), never answered with an empty relation.

Both indexes matter because they are different rows. Kauko Group Oy contains every token of Kauko Oy, so $has kauko AND $has oy covers both companies while {"vendor": "Kauko Oy"} covers one. A mined rule displayed as one company but supported by the other's invoices is a wrong number in a 200 — which is why $patterns offers the whole value alongside the tokens. A rule that pins a column to a whole value also drops that column's own token clauses from the same rule: they select nothing the value did not, and the rule reads as one company rather than as a list of its words. Members of a set column are never pruned this way — basket $has A AND basket $has B is the finding.

Itemset mining — relate: {"$patterns": …}

The market-basket primitive: which sets of a field's values co-occur more than chance. Candidates are the fields' MEMBERS — array/set elements, analyzed text tokens, or scalar values — plus, on a TEXT column, its whole values as well (see the section above) — mined greedily into variable-size itemsets (pairs seed, grow while the MDL gain holds, up to 6 items, support ≥ 2), then each itemset is scored against the where condition:

{ "from": { "from": "invoices", "where": { "customer_id": "CUST-0000" } },
  "where": { "gl_code": "1600" },
  "relate": { "$patterns": ["vendor", "category", "amount_band"] } }

Each hit carries related (the $and itemset), condition, lift, info, f (the itemset's support count) and n. Without a where, itemsets rank by their own support. The {"$related": {"relate": [...], "k": n, "to": {…}}} wrapper bounds the candidate set (k, default 32) and adds a narrowing target. Nested from composes (the multi-tenancy shape above): support counts and n are computed over the restricted population.

Not yet supported

• Nested from with plain relate — typed 501 (bit-universe alignment); relate $patterns DOES compose with nested from.
• basedOn $patterns (conjunction mining as predict evidence) — fails loud; planned.
• Arithmetic score composition beyond $multiply — v1's $divide, $subtract, $pow, $normalize and friends are not ported; only $multiply composes ranking values on v2 (let covers derived fields).

All of these fail loud rather than degrade — a 4xx/501 with the reason, never a silently wrong 200.

Related

• Inference (v2) · Schema Design (v2) · CollectionDb (v2)