Query language. Substrio docs

Send a query as POST /v1/:collection/_query. The body is the document itself, with no wrapper (see quickstart.md).

curl -s -X POST $SUBSTRIO_URL/v1/notes/_query -H "Authorization: Bearer $SUBSTRIO_KEY" \
  -H 'content-type: application/json' -d '{"where":{"n":{"$gt":2}},"limit":10,"skip":0}'
# {"data":[{"_id":"Qs7HRVu8oq","title":"world","n":5}],"count":1,"scanned":1,"has_more":false}

Comparison operators

Operator	Meaning
`$eq`	Equal
`$ne`	Not equal
`$gt`, `$gte`	Greater than, greater than or equal
`$lt`, `$lte`	Less than, less than or equal
`$in`, `$nin`	Value is (or is not) one of an array
`$startsWith`	String prefix match
`$regex`	Pattern match, with optional `$options: "i"`

{"where": {"city": {"$eq": "NYC"}, "age": {"$gte": 18}}}

Logical operators

{"where": {"$or": [{"city": {"$eq": "NYC"}}, {"city": {"$eq": "SF"}}]}}

$and requires every condition to match; $or requires any one of them. Several fields at the top level are implicitly ANDed: {"age": {"$gte": 18}, "city": {"$eq": "NYC"}} is the same as {"$and": [{"age": {"$gte": 18}}, {"city": {"$eq": "NYC"}}]}.

Pagination

{"where": {"active": {"$eq": true}}, "limit": 25, "skip": 50}

limit caps the rows returned; skip offsets into the matched set.

Geo queries

Find documents near a point, or within a box or a radius:

{"where": {"location": {"$near": {"lat": 40.7128, "lng": -74.0060, "k": 10}}}}

{"where": {"location": {"$geoWithin": {"$box": {"sw": {"lat": 40.0, "lng": -74.5}, "ne": {"lat": 41.0, "lng": -73.5}}}}}}

{"where": {"location": {"$geoWithin": {"$sphere": {"center": {"lat": 40.7128, "lng": -74.0060}, "radius": 5000}}}}}

$near answers the k nearest documents to lat/lng (default 10), or takes a point as {"$geometry": {"type": "Point", "coordinates": [lat, lng]}}: latitude first, the reverse of standard GeoJSON. $geoWithin with $box matches points between its sw and ne corners; with $sphere it matches points within radius of center.

Vector queries

{"where": {"embedding": {"$knn": {"vector": [0.1, 0.2, 0.3], "k": 10}}}}

{"where": {"embedding": {"$box": {"min": [0.0, 0.0, 0.0], "max": [1.0, 1.0, 1.0]}}}}

$knn finds the k nearest vectors to vector by the field's configured metric, for embeddings, recommendation and RAG lookups. $box on a vector field matches vectors inside the per-dimension min/max bounds. For a sparse vector ({"indices": [...], "values": [...]}), use $sparseKnn the same way as $knn. See storage/vectors-geo-rag.md for dimensions, providers and index state.

Aggregation

Count matches:

{"where": {"city": {"$eq": "NYC"}}, "count": true}

Response: {"count": 42}.

Fold a numeric field: send one of min, max, sum, avg, variance, stddev, distinct, percentile, top, bottom, histogram or range_count with the field name as its value. A query folds one field at a time.

{"where": {"active": {"$eq": true}}, "sum": "score"}

Response: {"field": "score", "result": 930, "count": 31, "type": "sum"}. avg adds an avg member alongside result's sum; percentile takes a rank (0 to 1); top and bottom take k (default 10); histogram takes buckets; range_count takes gte and lte.

Delete by query

{"where": {"status": {"$eq": "expired"}}, "$delete": true}

Response: {"deleted": 12, "matched": 12}. Each match is deleted the same way data.delete (DELETE /v1/{collection}/{id}) deletes one record (its ACL, indexes and triggers all apply).

For joins, grouping, sorting and set operations (union, intersect), send the same filter as a typed chain plan: see chains-over-api.md. For raw SQL over the same data, see sql-over-api.md.

Query language