Chain queries over the API

query.chain (MCP tool substrio_query_chain, POST /v1/_chain) runs the identical plan object a JS chain builds (db.people.eq(...).gt(...).sort(...).all(), see runtime/collections-and-chains.md) in the same engine, so a plan and its JS chain answer the same rows. Nothing here evaluates a condition itself.

Examples send Authorization: Bearer $SUBSTRIO_KEY. Collections are those of your app.

The plan

{"collection": "people",
 "where": "<node> | [<node>, ...]",
 "sort": {"field": "age", "direction": "desc"},
 "group_by": "team",
 "aggregates": [{"kind": "avg", "field": "age", "as": "avg_age"}],
 "having": "<node> | [<node>, ...]",
 "distinct": true,
 "compound": [{"kind": "union", "query": "<plan>"}],
 "skip": 0, "limit": 10,
 "select": ["name", "age"],
 "joins": [{"collection": "teams", "localField": "team", "foreignField": "code", "as": "team", "kind": "left"}],
 "terminal": "all",
 "field": "age"}

sort may also be an array of sort keys, or the alias sort_by; group_by may also be an array of fields.

Only collection is required. Unknown members are INVALID_ARGUMENT (400). A field name is top-level (use SQL for nested values), or alias.field of a join declared in joins (the join's as). The plan is built in the JS chain's order: joins first, then conditions, sort, select, groups, having, compound arms, skip and limit. A plan that is relational (any member below marked relational) runs through the SQL engine with SQL's semantics, exactly as the JS chain does (see runtime/collections-and-chains.md for the semantics of each).

where nodes

A leaf is {"op", "field", "value"}; a group is {"op", "conditions": [node, ...]}. An array of nodes is ANDed (each is one condition of the chain).

A value is null, a boolean, a number or a string (an array for in and nin, [low, high] for between). query is a plan: the members of this request except terminal and field; a subquery that is itself a compound is refused. not with several conditions is not(and(...)). Nesting is limited to 64 levels (400 beyond). The same strictness as the JS chain: an unknown op, a missing field, a nested field name, a wrong value shape is INVALID_ARGUMENT or MISSING_FIELD naming the member.

sort, skip, limit, select, joins, group_by

• sort: {"field", "direction"} or an array of them (the first is primary); direction 1, "asc", false ascending (default), -1, "desc", true descending. JS: .sort("age", -1). Several keys, or a joined field, are relational.
• skip, limit: integers from 0. JS: .skip(5).limit(10).
• select: the fields each returned document carries ("_id" is allowed). Naming a field of a join ("t.title") makes the chain answer plain rows (columns and rows, the columns keyed alias.field), as a grouped, aggregated, distinct or compound chain does; such a chain ends in all, first or count. Without a joined field in select, a join returns whole documents with the joined one under its as. JS: .select(["name"]).
• joins: each {"collection","localField","foreignField","as","kind"?}. Kind first (default): every result gains as, the first document of collection whose foreignField equals its localField (_id matches a document id), or null. Kind inner: a result per match, a document with none is dropped. Kind left: a result per match, a document with none stays with null. inner and left are relational. JS: .join(...), .innerJoin(...), .leftJoin("teams", "team", "code", "team").
• group_by: a field name: the aggregates and count answer per value of this field (terminal count, sum, avg, min or max). JS: .group_by("team"). An array of fields: the group keys of a relational plan, with aggregates (below). JS: .groupBy("team", "city").
• aggregates: [{"kind": sum|avg|min|max|count, "field"?, "as"?}], one result column each, named as, else count or <kind>_<field>; count takes no field. JS: .sum("age", "total"), .count() after groupBy.
• having: a condition (or an array, ANDed) over group keys and aggregate aliases: {"op": eq|neq|gt|gte|lt|lte, "field", "value"} (the symbols =, !=, >, >=, <, <= also). JS: .having("n", ">", 1).
• distinct: true, each row of select's fields once. JS: .distinct().
• compound: [{"kind": union|unionAll|intersect|except, "query": <plan>}]: this chain's rows and each arm's, paired by position. The chain's own sort, skip and limit apply to the whole; an arm has no sort, skip, limit or compound of its own (refused). JS: .union(chain), .unionAll(...), .intersect(...), .except(...).

A relational plan that groups, aggregates, is distinct or compound ends in all, first or count and answers plain rows (below); sum, avg, min and max terminals of a relational plan are refused (use aggregates), and a group_by field name cannot mix with relational members (joins, distinct, group keys, aggregates, compounds): it is refused as an invalid argument that names them. db.arg placeholders have no plan form: a plan carries its values.

terminals

terminal (default all), with the JS call and the response:

matched is the rows the conditions select before skip/limit. A document is the record as a read of the collection answers it (with its _id); with select or joins it is the plain object {"_id"?, <selected fields>, <join "as" documents>}. Group keys are typed (a number stays a number, a missing field and null are the one null group). Only numbers fold: strings, booleans and dates are skipped.

Examples

Londoners by age, oldest first (JS: db.people.eq("city","London").sort("age",-1).all()):

{"collection":"people","where":[{"op":"eq","field":"city","value":"London"}],
 "sort":{"field":"age","direction":"desc"}}

{"documents":[{"_id":"...","name":"Cy","age":41},{"_id":"...","name":"Ada","age":36}],
 "count":2,"matched":2}

Named fields with a join (JS: db.people.eq("name","Ada").select(["name"]).join("teams","team","code","team").all()):

{"collection":"people","where":{"op":"eq","field":"name","value":"Ada"},
 "select":["name"],"joins":[{"collection":"teams","localField":"team","foreignField":"code","as":"team"}]}

{"documents":[{"name":"Ada","team":{"code":"t1","title":"Core"}}],"count":1,"matched":1}

Sum of age among the active (JS: db.people.eq("active",true).sum("age")):

{"collection":"people","where":{"op":"eq","field":"active","value":true},"terminal":"sum","field":"age"}

{"value":132}

Count per team (JS: db.people.group_by("team").count()):

{"collection":"people","group_by":"team","terminal":"count"}

{"groups":[{"key":"t1","value":2},{"key":"t2","value":1}],"count":5}

Join kinds (JS: db.people.leftJoin("teams","team","code","team").all()), every person once per team match, null when none:

{"collection":"people","joins":[{"collection":"teams","localField":"team","foreignField":"code","as":"team","kind":"left"}]}

{"documents":[{"_id":"...","name":"Ada","team":{"code":"t1","title":"Core"}}],"count":2,"matched":2}

Subquery (JS: db.people.inQuery("team", db.teams.eq("title","Core"), "code").all()):

{"collection":"people","where":{"op":"inQuery","field":"team",
 "query":{"collection":"teams","where":{"op":"eq","field":"title","value":"Core"}},"queryField":"code"}}

Compound (JS: db.people.select("name").union(db.contacts.select("name")).all()):

{"collection":"people","select":["name"],
 "compound":[{"kind":"union","query":{"collection":"contacts","select":["name"]}}]}

{"rows":[["Ada"],["Cy"]],"columns":[{"name":"name","type":"TEXT"}],"count":2}

Group with having (JS: db.people.groupBy("team").count("n").having("n", ">", 1).all()):

{"collection":"people","group_by":["team"],"aggregates":[{"kind":"count","as":"n"}],
 "having":{"op":"gt","field":"n","value":1}}

{"rows":[["t1",2]],"columns":[{"name":"team","type":"TEXT"},{"name":"n","type":"INTEGER"}],"count":1}

Access

The caller needs read access to the collection (and to each joined collection), as for GET. An unknown collection or join target is COLLECTION_NOT_FOUND (404). Where a collection has record-level rules, the caller's own records only are returned (a record the caller cannot read is left out of documents, ids and first; count and matched in those responses report what the caller may read); count, sum, avg, min, max and grouped terminals are refused (403) for a caller that cannot bypass those rules, since they would reveal rows the caller may not read; so is a relational plan that groups, aggregates, is distinct or compound, and a plan whose subquery or compound arm reads a collection with such rules. Each collection a join, subquery or arm reads needs read access too. The app's own key administers the app and sees everything.