Query operations. Substrio docs

`data`

`data.query`

Run a typed query over one collection: a where filter, sort, limit and offset (see the schema); use_index false forces a scan. Answers the matching records. For joins, grouping and aggregates use query.chain; for SQL use query.sql.

Route: POST /v1/{collection}/_query
GET /v1/{collection}/_query
ANY query
Auth: App key or user session; your security rules apply none
MCP tool: substrio_query
CLI: substrio op data.query

Parameters

Name	In	Type	Required	Description
`collection`	path	`string`	Yes	The collection's name, as `collection.list` names it.
`use_index`	query	`boolean`	No	false runs this one query without the collection's index (a scan, never cached), to explain or debug a result against the indexed one.

Response

A JSON object.

Errors

A failed call answers the error envelope with a typed code.

Example

curl -X POST "$SUBSTRIO_URL/v1/$COLLECTION/_query" \
  -H "Authorization: Bearer $SUBSTRIO_KEY"

`query`

`query.aggregate`

Run an aggregation pipeline over one collection: stages run in order, one operator per stage (see the schema); use_index false forces a scan. For SQL-style grouping with having use query.chain.

Route: POST /v1/{collection}/_aggregate
ANY aggregate
Auth: App key tenant
MCP tool: substrio_pipeline
CLI: substrio op query.aggregate

Parameters

Name	In	Type	Required	Description
`collection`	path	`string`	Yes	The collection's name, as `collection.list` names it.
`use_index`	query	`boolean`	No	false runs this one query without the collection's index (a scan, never cached), to explain or debug a result against the indexed one.

Response

A JSON object.

Errors

A failed call answers the error envelope with a typed code.

Example

curl -X POST "$SUBSTRIO_URL/v1/$COLLECTION/_aggregate" \
  -H "Authorization: Bearer $SUBSTRIO_KEY"

`query.chain`

Run a typed chain query over a collection: the JS chain's methods as JSON (conditions eq, neq, gt, gte, lt, lte, in, nin, iin, ieq, exists, startsWith, istartsWith, endsWith, contains, icontains (served by a declared keyword index), like, ilike, regex, iregex, size, sizeGt, sizeLt, containsAll, containsAny, between, eqField and the other field comparisons (value names the other field), inQuery, notInQuery, existsQuery, and, or, not; elemMatch is JS only; joins of kind first, inner or left; sort keys, skip, limit, select, distinct; group_by with aggregates and having; compound union, unionAll, intersect, except; terminals all, first, count, ids, sum, avg, min, max). A field may be alias.field of a join. A grouped, aggregated, distinct or compound plan answers plain rows as sql does (rows, columns).

Route: POST /v1/_chain
Auth: App key tenant
MCP tool: substrio_query_chain
CLI: substrio op query.chain

Parameters

Name	In	Type	Required	Description
`collection`	body	`string`	Yes	the collection to read.
`where`	body	`any`	No	one condition node, or an array of nodes that are ANDed: a condition node {op, field, value} (value: null, boolean, number or string; an array for in and nin; [low, high] for between; optional boolean for exists), {op: inQuery\|notInQuery, field, query, queryField}, {op: existsQuery, query} or {op: and\|or\|not, conditions: [node, ...]}.
`sort`	body	`any`	No	one sort key, or an array of keys (the first is primary; several keys answer by SQL's order: nulls last, ties in row order).
`sort_by`	body	`object`	No	alias of sort.
`group_by`	body	`any`	No	a field name: sum, avg, min, max and count answer per value of this field; or an array of field names (alias.field allowed): the group keys, with aggregates, answering plain rows.
`aggregates`	body	`array of object`	No	the aggregates of an array group_by, one result column each.
`having`	body	`any`	No	conditions over group keys and aggregate aliases, ANDed: {op: eq\|neq\|gt\|gte\|lt\|lte (or = != > >= < <=), field, value}, or an array of them.
`distinct`	body	`boolean`	No	each row of select once (answers plain rows).
`compound`	body	`array of object`	No	this chain's rows and each arm's, paired by position; the chain's sort, skip and limit apply to the whole; an arm has no sort, skip, limit or compound of its own.
`skip`	body	`integer`	No	Minimum 0.
`limit`	body	`integer`	No	Minimum 0.
`select`	body	`array of string`	No	the fields each document carries (alias.field of a join answers plain rows).
`joins`	body	`array of object`	No
`terminal`	body	`"all" \| "first" \| "count" \| "ids" \| "sum" \| "avg" \| "min" \| "max"`	No	what the chain ends in (default all).
`field`	body	`string`	No	the field sum, avg, min and max fold.

Response

Field	Type	Present
`documents`	`array of object`	When set
`document`	`object or null`	When set
`ids`	`array of string`	When set
`count`	`integer`	When set
`matched`	`integer`	When set
`value`	`number or null`	When set
`groups`	`array of object`	When set

Errors

A failed call answers the error envelope with a typed code.

Example

curl -X POST "$SUBSTRIO_URL/v1/_chain" \
  -H "Authorization: Bearer $SUBSTRIO_KEY" \
  -H "Content-Type: application/json" \
  -d '{"collection":"collection"}'

`query.count`

Count the records of a collection that match a where filter, without returning them; use_index false forces a scan.

Route: GET /v1/{collection}/_count
ANY count
Auth: App key tenant
MCP tool: substrio_count
CLI: substrio op query.count

Parameters

Name	In	Type	Required	Description
`collection`	path	`string`	Yes	The collection's name, as `collection.list` names it.
`use_index`	query	`boolean`	No	false runs this one query without the collection's index (a scan, never cached), to explain or debug a result against the indexed one.

Response

A JSON object.

Errors

A failed call answers the error envelope with a typed code.

Example

curl "$SUBSTRIO_URL/v1/$COLLECTION/_count" \
  -H "Authorization: Bearer $SUBSTRIO_KEY"

`query.multi`

Run several queries over different collections in one call and answer each result in order.

Route: POST /v1/_multi_search
Auth: App key or user session; your security rules apply none
MCP tool: substrio_multi_search
CLI: substrio op query.multi

Parameters

Name	In	Type	Required	Description
`collections`	body	`array of string`	Yes
`query`	body	`string`	Yes
`limit`	body	`integer`	No	10 when absent.
`search_k`	body	`integer`	No	limit times 2 when absent.
`mode`	body	`string`	No

Response

Field	Type	Present
`data`	`array of object`	Always
`count`	`integer`	Always
`total_searched`	`integer`	Always
`collections_searched`	`array of string`	Always
`collections_skipped`	`array of string`	Always

Errors

A failed call answers the error envelope with a typed code.

Example

curl -X POST "$SUBSTRIO_URL/v1/_multi_search" \
  -H "Authorization: Bearer $SUBSTRIO_KEY" \
  -H "Content-Type: application/json" \
  -d '{"collections":[],"query":"query"}'

`query.qx`

Execute a parser-owned query plan.

Route: POST /v1/{collection}/_qx
Auth: App key or user session; your security rules apply none
CLI: substrio op query.qx

Parameters

Name	In	Type	Required	Description
`collection`	path	`string`	Yes	The collection's name, as `collection.list` names it.

Response

A JSON object.

Errors

A failed call answers the error envelope with a typed code.

Example

curl -X POST "$SUBSTRIO_URL/v1/$COLLECTION/_qx" \
  -H "Authorization: Bearer $SUBSTRIO_KEY"

`query.sql`

Run one SQL statement with bound parameters.

Route: POST /v1/_sql
Auth: App key tenant
MCP tool: substrio_sql
CLI: substrio op query.sql

Parameters

Name	In	Type	Required	Description
`sql`	body	`string`	Yes	one SQL statement; ? marks a bound parameter.
`params`	body	`array of string or number or boolean or null`	No	values bound to the placeholders in order.
`table`	body	`string`	No	the table a SELECT with no FROM clause reads.
`limit`	body	`integer`	No	the most rows per page (default 1000); more rows answer next_cursor. Minimum 1.
`cursor`	body	`string`	No	the previous page's next_cursor, sent with the same statement and parameters; it reads the result the first page ran (no re-run), held until its last page is read or 5 idle minutes pass, then CURSOR_INVALIDATED.

Response

Field	Type	Present
`returns_rows`	`boolean`	Always
`columns`	`array of object`	Always
`rows`	`array of array of string or number or boolean or null`	Always
`offset`	`integer`	Always
`total_rows`	`integer`	Always
`next_cursor`	`string or null`	Always
`changes`	`integer`	Always
`last_insert_id`	`integer`	Always

Errors

A failed call answers the error envelope with a typed code.

Example

curl -X POST "$SUBSTRIO_URL/v1/_sql" \
  -H "Authorization: Bearer $SUBSTRIO_KEY" \
  -H "Content-Type: application/json" \
  -d '{"sql":"sql"}'

`query.stream`

Stream typed query results over WebSocket.

Route: GET /v1/query/ws
Auth: App key or user session; your security rules apply none
CLI: substrio op query.stream

Parameters

Name	In	Type	Required	Description
`collection`	query	`string`	Yes
`where`	query	`object`	No
`select`	query	`array`	No
`limit`	query	`integer`	No	Minimum 1.
`skip`	query	`integer`	No	Minimum 0.
`sort`	query	`object`	No

Response

A JSON object.

Errors

A failed call answers the error envelope with a typed code.

Example

curl "$SUBSTRIO_URL/v1/query/ws" \
  -H "Authorization: Bearer $SUBSTRIO_KEY"

`sql`

`sql.batch`

Run SQL statements with bound parameters in order in one transaction; a failure rolls every row write back and names the failing statement's index, errno, sqlstate and message.

Route: POST /v1/_sql/batch
Auth: App key tenant
MCP tool: substrio_sql_batch
CLI: substrio op sql.batch

Parameters

Name	In	Type	Required	Description
`statements`	body	`array of object`	Yes	statements run in order in one transaction on one connection; the first failure rolls every earlier one back and names its index.

Response

Field	Type	Present	Description
`results`	`array of object`	Always
`count`	`integer`	Always

Errors

A failed call answers the error envelope with a typed code.

Example

curl -X POST "$SUBSTRIO_URL/v1/_sql/batch" \
  -H "Authorization: Bearer $SUBSTRIO_KEY" \
  -H "Content-Type: application/json" \
  -d '{"statements":[]}'