Query and SQL

POST /v2/query and POST /v2/sql read many drops at once — filters, sorting, limits, aggregates and joins, plus what the scanned/matched/truncated/complete counters actually mean and where they cannot be trusted.

What these two endpoints do

POST /v2/query and POST /v2/sql both read many drops at once. They scan the current version of every entity under one route (or, for /v2/query, under a tree of routes), keep the rows that match your condition, and return them. Neither endpoint writes anything.

Pick /v2/query when you want a JSON body you can build programmatically, nested field access, aggregates, or a join. Pick /v2/sql when you want to type a SELECT by hand and get back column/row arrays instead of objects.

Both require X-Ocean-Key, and both check read access on the route named in the request. A route your key may not read returns 403 FORBIDDEN — not an empty result.

Neither door takes URL query parameters. Everything goes in the JSON body. Appending any parameter to the URL is a hard 400, so /v2/query?limit=5 will not quietly ignore the parameter — it fails the whole request:

curl -s -X POST "https://api2.infiniteocean.io/v2/query?limit=5" \
  -H "Content-Type: application/json" -H "X-Ocean-Key: $OCEAN_KEY" \
  -d '{"prefix":"_docs_probe/query/orders","limit":2}'

400  {"ok":false,"error":"unknown query parameter \"limit\" — nothing was applied",
      "code":"UNKNOWN_PARAM"}

Both are POST only. GET /v2/query returns 404 {"ok":false,"error":"no /v2 endpoint matches this method and path","code":"NOT_FOUND"}.

The sample route used on this page

Every request and response below was run against a live route. To reproduce them, write the same drops under a prefix your key owns, then substitute that prefix. The first write to a route creates its access rule — the drop response says "rule_created":true on that first write and false on every later one.

curl -s -X POST https://api2.infiniteocean.io/v2/drop \
  -H "Content-Type: application/json" -H "X-Ocean-Key: $OCEAN_KEY" \
  -d '{"route":"_docs_probe/query/orders","key":"o1",
       "payload":{"customer":"alice","amount":120,"status":"paid","shipped":true,
                  "meta":{"region":"eu","tier":2}}}'

The five rows used throughout, with the ts each one carried at the time these responses were captured (o1 had been rewritten, so its ts is the newest — that matters for the from and last examples):

key	ts	customer	amount	status	shipped	meta
o1	1787593824762	alice	120	paid	true	{region: eu, tier: 2}
o2	1787591981613	bob	45	open	false	{region: us, tier: 1}
o3	1787591981916	alice	300	paid	true	{region: eu, tier: 3}
o4	1787591983650	carol	80	open	false	{region: us, tier: 2}
o5	1787591985670	bob	210	paid	true	{region: apac, tier: 1}

POST /v2/query

Smallest working request

curl -s -X POST https://api2.infiniteocean.io/v2/query \
  -H "Content-Type: application/json" -H "X-Ocean-Key: $OCEAN_KEY" \
  -d '{"prefix":"_docs_probe/query/orders","filter":{"status":"paid"},
       "limit":10,"select":["key","status"]}'

Real response, 200, in full:

{"ok":true,"scope":"squery.join_s1",
 "results":[{"key":"o1","status":"paid"},{"key":"o3","status":"paid"},{"key":"o5","status":"paid"}],
 "drops":[{"key":"o1","status":"paid"},{"key":"o3","status":"paid"},{"key":"o5","status":"paid"}],
 "scanned":5,"matched":3,"returned":3,"truncated":false,"complete":true,
 "catalog_complete":true,"time_ms":1097}

drops is a byte-identical copy of results, kept for older clients. Read results. Later examples on this page elide the drops copy; that is the only edit made to any response shown here.

Request body

Field	Type	Required	Meaning
`prefix`	string	yes	The route to scan. `route` is accepted as an alias. Ends in `/*` to scan all descendant routes.
`filter`	object	no	Field conditions, ANDed. No filter means every row matches.
`sort`	object	no	Exactly one `{"field": 1}` (ascending) or `{"field": -1}` (descending).
`limit`	integer	no	Rows to return. Default 100.
`select`	string[]	no	Top-level payload fields to keep, plus `"key"`.
`from`	number or string	no	Keep rows whose `ts` is strictly greater. Epoch milliseconds, or `"YYYY-MM-DD"`.
`last`	integer	no	Keep the N newest matching rows by `ts`.
`aggregate`	object	no	`{group_by?, compute}`. Switches the response to aggregate mode.
`group_by` / `compute`	string / object	no	The same aggregate, spelled at the top level instead of nested.
`join`	object	no	Left join onto a second route by entity key.

Unknown top-level body fields are ignored rather than rejected. {"prefix":"_docs_probe/query/orders","bogus":1,"limit":2,"select":["key"]} returned 200 and a normal result set. Do not read that as support for a field — check that the effect you wanted actually happened. (URL query parameters behave the opposite way and 400; see above.)

filter

A filter entry is either a bare literal (meaning $eq) or a single-operator object. Multiple entries are ANDed. One operator per field.

"filter": {"status": "paid"}                           equality (shorthand)
"filter": {"amount": {"$eq": 300}}                     equality (explicit)
"filter": {"amount": {"$gt": 100}}                     one operator
"filter": {"status": "paid", "amount": {"$gt": 150}}   AND

Operators: $eq, $ne, $gt, $gte, $lt, $lte. There is no $or, no $in, and no $contains — each returns 400 UNSUPPORTED_QUERY.

The entity key is filterable under the reserved name key:

curl -s -X POST https://api2.infiniteocean.io/v2/query \
  -H "Content-Type: application/json" -H "X-Ocean-Key: $OCEAN_KEY" \
  -d '{"prefix":"_docs_probe/query/orders","filter":{"key":{"$gte":"o3"}},
       "limit":10,"select":["key"]}'

{"ok":true,"scope":"squery.join_s1",
 "results":[{"key":"o3"},{"key":"o4"},{"key":"o5"}],
 "scanned":5,"matched":3,"returned":3,"truncated":false,"complete":true,
 "catalog_complete":true,"time_ms":941}

A payload. prefix on a field name is stripped before matching. Verified back to back: {"amount":120} and {"payload.amount":120} both returned [{"key":"o1"}] with "matched":1.

Nested payload fields

A filter field may contain one dot, which reaches into a nested object one level down. This was measured, not assumed:

curl -s -X POST https://api2.infiniteocean.io/v2/query \
  -H "Content-Type: application/json" -H "X-Ocean-Key: $OCEAN_KEY" \
  -d '{"prefix":"_docs_probe/query/orders","filter":{"meta.region":"eu"},
       "limit":10,"select":["key","meta"]}'

{"ok":true,"scope":"squery.join_s1",
 "results":[{"key":"o1","meta":{"region":"eu","tier":2}},
            {"key":"o3","meta":{"region":"eu","tier":3}}],
 "scanned":5,"matched":2,"returned":2,"truncated":false,"complete":true,
 "catalog_complete":true,"time_ms":1027}

Two dots do not work. A drop with payload {"name":"deep","a":{"b":{"c":1}},"tags":["x","y"]} was written to _docs_probe/query/deep, and {"filter":{"a.b.c":1}} returned 200 with "matched":0 — no error, no rows, while an unfiltered query on the same route returned the row. Same for {"a.b":"x"}, because the value at a.b is an object and only strings, numbers, booleans and null are comparable. A too-deep path fails silently as an empty result. If you need to filter on something three levels down, flatten it into a top-level or one-level-nested field when you write the drop.

Type matching is strict

A number in the payload only matches a JSON number in the filter, and a string only matches a string. There is no coercion and no error — the mismatch returns zero rows. Both of these were run back to back:

# string literal against a numeric field
-d '{"prefix":"_docs_probe/query/orders","filter":{"amount":"120"},"limit":10,"select":["key"]}'
200  {"results":[], "scanned":5,"matched":0,"returned":0, …}

# number literal against the same field
-d '{"prefix":"_docs_probe/query/orders","filter":{"amount":120},"limit":10,"select":["key"]}'
200  {"results":[{"key":"o1"}], "scanned":5,"matched":1,"returned":1, …}

Booleans compare against true / false and support only $eq and $ne; a null field compares against null, also equality only. Both verified against a route holding {"note":null,"flag":true} (key n1) and {"note":"set","flag":false} (key n2):

-d '{"prefix":"_docs_probe/query/nulls","filter":{"note":null},"limit":10}'
200  "results":[{"key":"n1","ts":1787592843198,"payload":{"note":null,"flag":true}}],"matched":1

-d '{"prefix":"_docs_probe/query/nulls","filter":{"flag":{"$ne":true}},"limit":10}'
200  "results":[{"key":"n2","ts":1787592843537,"payload":{"note":"set","flag":false}}],"matched":1

Arrays and objects stored in a field are not comparable to anything and never match. A field that is absent from a row never matches either, including with $ne — so a $ne filter will not surface rows that lack the field entirely:

-d '{… "filter":{"nonexistent":{"$ne":"x"}}, "select":["key"]}'
200  "results":[],"scanned":5,"matched":0

-d '{… "filter":{"status":{"$ne":"x"}}, "select":["key"]}'          # control
200  "results":[{"key":"o1"},…,{"key":"o5"}],"scanned":5,"matched":5

sort

One field only. 1 ascending, -1 descending. Two fields return 400 UNSUPPORTED_QUERY. Sorting happens over the whole matched set, before limit cuts it, so sort + limit gives you a genuine top-N.

curl -s -X POST https://api2.infiniteocean.io/v2/query \
  -H "Content-Type: application/json" -H "X-Ocean-Key: $OCEAN_KEY" \
  -d '{"prefix":"_docs_probe/query/orders","sort":{"amount":-1},
       "limit":2,"select":["key","amount"]}'

{"ok":true,"scope":"squery.join_s1",
 "results":[{"key":"o3","amount":300},{"key":"o5","amount":210}],
 "scanned":5,"matched":5,"returned":2,"truncated":true,"complete":true,
 "catalog_complete":true,"time_ms":1037}

Confirmed on a larger route too: {"sort":{"n":-1},"limit":5} over a 230-row route returned b329…b325 with "scanned":230,"matched":230,"returned":5 — the top 5 of all 230, not the top 5 of the first page read.

Without sort, rows arrive in storage enumeration order. Do not depend on it; add a sort if order matters to you.

limit

Default 100, measured on a 230-row route: omitting limit returned "returned":100,"matched":230,"truncated":true. "limit":0 is legal and returns "results":[] with matched still counted — a cheap way to count matches without transferring rows. Values larger than the match count return everything and do not error: on the same 230-row route both "limit":250 and "limit":99999 returned all 230 rows with "truncated":false.

select

select keeps the named top-level payload fields plus the reserved key. Anything else you name comes back as null, including dotted paths and ts:

-d '{"prefix":"_docs_probe/query/orders","select":["key","meta.region"],"limit":3}'
200  "results":[{"key":"o1","meta.region":null},
                 {"key":"o2","meta.region":null},
                 {"key":"o3","meta.region":null}]

-d '{"prefix":"_docs_probe/query/orders","select":["key","ts"],"limit":2}'
200  "results":[{"key":"o1","ts":null},{"key":"o2","ts":null}]

To get a nested value, select its parent object ("select":["key","meta"]) and read into it client-side. When select is omitted, each row carries key, ts (epoch milliseconds) and the whole payload.

from and last

from keeps rows whose ts is strictly greater than the cutoff. It takes epoch milliseconds or a YYYY-MM-DD date. Verified in both directions on the five rows and timestamps in the table above:

-d '{"prefix":"_docs_probe/query/orders","from":"2026-08-23","select":["key"],"limit":10}'
200  "matched":5   (all five rows are newer)

-d '{"prefix":"_docs_probe/query/orders","from":"2026-08-25","select":["key"],"limit":10}'
200  "matched":0   (none are)

-d '{"prefix":"_docs_probe/query/orders","from":1787591983000,"select":["key"],"limit":10}'
200  "results":[{"key":"o1"},{"key":"o4"},{"key":"o5"}],"matched":3

The epoch example returns o1 because o1 was rewritten and now carries the newest ts of the five — a live demonstration of the next paragraph.

last: N keeps the N newest matching rows by ts. {"last":2,"select":["key"]} returned [{"key":"o1"},{"key":"o5"}] — the two newest timestamps, not the two highest keys.

Neither is a history log. Both operate on the current version of each entity, so a drop written five times still contributes one row, carrying the timestamp of its latest write.

Wildcard prefixes

A prefix ending in /* scans every descendant route under that parent, at any depth. Each row then also carries a route field naming where it came from:

curl -s -X POST https://api2.infiniteocean.io/v2/query \
  -H "Content-Type: application/json" -H "X-Ocean-Key: $OCEAN_KEY" \
  -d '{"prefix":"_docs_probe/query/*","filter":{"amount":{"$gte":300}},"limit":5}'

{"ok":true,"scope":"squery.join_s1",
 "results":[{"key":"o3","route":"_docs_probe/query/orders","ts":1787591981916,
             "payload":{"customer":"alice","amount":300,"status":"paid","shipped":true,
                        "meta":{"region":"eu","tier":3}}}],
 "scanned":248,"matched":1,"returned":1,"truncated":false,"complete":true,
 "catalog_complete":false,"time_ms":13932}

scanned is the total across every descendant route, so it grows with everything under the parent — 248 here, against 5 for the same filter on the single orders route. A bare "prefix":"*" is refused with 403 FORBIDDEN.

Wildcards are much slower. Two runs of the query above took 13.9 s and 16.6 s, against roughly 1 s for the same filter on one exact route. Prefer exact routes.

Aggregates

Adding aggregate (or a top-level compute) folds the matched rows instead of listing them. compute maps an output name to one operator: {"$count":true}, or {"$sum"|"$avg"|"$min"|"$max": "field"}. group_by is optional; without it you get one row for everything.

curl -s -X POST https://api2.infiniteocean.io/v2/query \
  -H "Content-Type: application/json" -H "X-Ocean-Key: $OCEAN_KEY" \
  -d '{"prefix":"_docs_probe/query/orders",
       "aggregate":{"group_by":"customer",
                    "compute":{"total":{"$sum":"amount"},
                               "n":{"$count":true},
                               "avg_amount":{"$avg":"amount"}}}}'

{"ok":true,"mode":"aggregate","scope":"squery.join_s1","partial":false,
 "results":[{"customer":"alice","total":420,"n":2,"avg_amount":210},
            {"customer":"bob","total":255,"n":2,"avg_amount":127.5},
            {"customer":"carol","total":80,"n":1,"avg_amount":80}],
 "groups":[…identical copy of results…],
 "scanned":5,"matched":5,"returned":3,"truncated":false,"complete":true,
 "catalog_complete":true,"time_ms":2904}

In aggregate mode the response gains "mode":"aggregate" and partial, and groups mirrors results. returned is the number of groups.

Do not read scanned and matched as row counts in aggregate mode. The same aggregate request, repeated six times with no writes in between, returned identical results every time but reported "scanned":5,"matched":5 on the first run (2904 ms) and "scanned":0,"matched":1 on all five that followed (670–1377 ms). Those two counters describe how the answer was produced on that request, not how many rows exist. If you need a row count, ask for it as {"$count":true} in compute — that value was correct on every run.

filter applies before the fold — {"filter":{"status":"paid"},"aggregate":{"compute":{"n":{"$count":true},"total":{"$sum":"amount"}}}} returned [{"n":3,"total":630}] with "scanned":5,"matched":3. The flat spelling works too: {"group_by":"status","compute":{"n":{"$count":true}}} returned [{"status":"open","n":2},{"status":"paid","n":3}].

group_by without compute is refused with 400 UNSUPPORTED_QUERY rather than silently returning a plain list.

Aggregates work across more than one internal read: an aggregate over a 230-row route returned {"n":230,"max_n":329,"min_n":100} with "scanned":230,"matched":230,"complete":true, matching the data written.

Joins

A join left-joins each returned row onto a second route by that route's entity key. on is [localField, "key"]: the local field holds the remote key. Optional select picks fields out of the remote payload; omit it to get the whole remote payload.

curl -s -X POST https://api2.infiniteocean.io/v2/query \
  -H "Content-Type: application/json" -H "X-Ocean-Key: $OCEAN_KEY" \
  -d '{"prefix":"_docs_probe/query/invoices",
       "join":{"prefix":"_docs_probe/query/users",
               "on":["payload.user_id","key"],
               "select":["payload.name","payload.plan"]},
       "limit":10}'

{"ok":true,"scope":"squery.join_s1",
 "results":[{"_joined":{"name":"Alice Ross","plan":"pro"},"key":"i1","ts":1787592186446,
             "payload":{"user_id":"u_alice","amount":10}},
            {"_joined":{"name":"Bob Kane","plan":"free"},"key":"i2","ts":1787592187942,
             "payload":{"user_id":"u_bob","amount":20}},
            {"_joined":{"name":"Alice Ross","plan":"pro"},"key":"i3","ts":1787592188194,
             "payload":{"user_id":"u_alice","amount":30}}],
 "scanned":3,"matched":3,"returned":3,"truncated":false,"complete":true,
 "catalog_complete":true,"time_ms":1846,"joins_resolved":2}

The joined data lands under _joined. joins_resolved counts distinct remote lookups — 2, not 3, because two invoices share a user.

The remote side must be "key". Joining on a remote payload field returns 400 JOIN_REMOTE_NOT_KEY:

{"ok":false,
 "error":"join.on[1] must be \"key\" — the remote side of a join is the entity key. Matching an
          arbitrary remote field would scan the whole remote route on every query. Key your remote
          route by the value you join on (users @<user_id>), then join on
          [\"payload.user_id\", \"key\"].",
 "code":"JOIN_REMOTE_NOT_KEY","scope":"squery.join_s1"}

So design the remote route with the join value as its key. A join combined with an aggregate is refused with 400 UNSUPPORTED_QUERY.

Response fields

Field	Meaning
`ok`	`true` on success, `false` on every error body.
`scope`	Always `"squery.join_s1"` on success, and on the 400s this engine raises itself (`UNSUPPORTED_QUERY`, `JOIN_REMOTE_NOT_KEY`). It is absent from `401` and `403` bodies, which are produced before the engine runs.
`results`	The rows, or the groups in aggregate mode.
`drops` / `groups`	Identical copies of `results`, for older clients. Ignore them.
`scanned`	Rows read from storage before filtering. Not meaningful in aggregate mode — see above.
`matched`	Rows that passed the filter, before `limit`. Not meaningful in aggregate mode — see above.
`returned`	Rows (or groups) actually in `results`.
`truncated`	`true` if `returned < matched` or the scan did not certify it reached the end. See below.
`complete`	`true` only when the scan certifiably reached the end of the route.
`catalog_complete`	Present on every `/v2/query` response, and always `true` for an exact route. On a wildcard it reports whether the route directory could enumerate every descendant route; `false` means more routes may exist, not that this scan was short.
`time_ms`	Server-side time for the query.
`joins_resolved`	Present only with a join: distinct remote key lookups performed.
`mode`, `partial`	Present only in aggregate mode.

Reading truncated and complete

This is the part that will bite you if you skim it.

truncated is the single field that means "your answer is short". It is the OR of two separate conditions: returned < matched, and the scan not certifying that it reached the end. Only truncated covers both — if you page by comparing returned to matched, a short scan looks consistent and complete while being wrong.

complete: false does not mean rows are missing. It appears reliably on a route whose index has not been folded yet. Measured on a route created moments earlier, through both doors:

# two drops written to a brand-new route, queried ~4 s later
SELECT * FROM `_docs_probe/query/disjoint` WHERE key >= ''
200  {"row_count":2,"scanned":2,"matched":2,"returned":2,
      "truncated":true,"complete":false, …}    ← both rows present anyway

The same five-row orders route, long settled, returned "truncated":false,"complete":true on twelve consecutive identical runs, and GET /v2/list on it reported "complete":true,"index":true at the same moment. Rewriting a row on an already-folded route did not flip it back: complete stayed true on ten polls over 20 seconds after an overwrite.

Practical handling: treat complete: false on a route you have only just started writing to as "not yet certified", and retry if certainty matters. Treat complete: false together with returned == limit as a genuinely short scan.

Errors

Status	code	When
400	`UNKNOWN_PARAM`	Any URL query parameter. This door takes none.
400	`MISSING_FIELD`	No `prefix` or `route`. A body that is not valid JSON also lands here: `"body must include prefix (or route) — nothing was applied"`.
400	`UNSUPPORTED_QUERY`	`$or`, `$in`, `$contains`, `offset`, multi-field `sort`, `group_by` without `compute`, join plus aggregate. All six return the identical body; the error text lists the whole supported scope.
400	`JOIN_REMOTE_NOT_KEY`	`on[1]` is not `"key"`.
400	`INVALID_INPUT`	A field has the wrong JSON type. All three of `"limit":"5"`, `"select":"key"` and `"limit":-1` returned `{"ok":false,"error":"invalid query body — nothing was applied","code":"INVALID_INPUT"}`.
401	`KEY_REQUIRED`	No `X-Ocean-Key` header.
401	`UNKNOWN_KEY`	Header present, key not recognised.
403	`FORBIDDEN`	Your key may not read that route. A route that does not exist also returns 403, not 404 — there is no rule granting you access to it. A bare `"prefix":"*"` is the same.
404	`NOT_FOUND`	Wrong method — this path exists for `POST` only.
409	`AGGREGATE_NEEDS_INDEX`	Declared, not observed in these probes. The route is larger than a single scan can cover, so any total would be wrong rather than partial; the query is valid and answers once the route is folded.
503	`NOT_READY`	Declared, not observed in these probes. Storage backend not wired on the node that answered.

# unsupported operator, real response
-d '{"prefix":"_docs_probe/query/orders","filter":{"status":{"$in":["paid"]}},"limit":5}'
400
{"ok":false,
 "error":"unsupported structured query — v2 scope is prefix(exact or parent/* via route
          directory)+filter($eq|$ne|$gt|$gte|$lt|$lte)+single sort+limit+select+from+last+
          aggregate(group_by+compute $count|$sum|$avg|$min|$max)+left join on the remote entity
          key; no offset/$or/$in, and no join together with aggregate (scope=squery.join_s1)",
 "code":"UNSUPPORTED_QUERY","scope":"squery.join_s1"}

POST /v2/sql

Smallest working request

The body is exactly {"query": "<sql>"} and nothing else.

curl -s -X POST https://api2.infiniteocean.io/v2/sql \
  -H "Content-Type: application/json" -H "X-Ocean-Key: $OCEAN_KEY" \
  -d '{"query":"SELECT * FROM \"_docs_probe/query/orders\" WHERE status = '"'"'paid'"'"'"}'

{"ok":true,"command":"SELECT","scope":"select_cols_where",
 "columns":["key","customer","amount","status","shipped","meta"],
 "rows":[["o1","alice",120,"paid",true,{"region":"eu","tier":2}],
         ["o3","alice",300,"paid",true,{"region":"eu","tier":3}],
         ["o5","bob",210,"paid",true,{"region":"apac","tier":1}]],
 "row_count":3,"scanned":5,"matched":3,"returned":3,
 "truncated":false,"complete":true,"time_ms":1040}

Results are column-oriented: columns names the fields, rows holds arrays in that order. With SELECT *, columns is the union of key and every payload field seen across the matched rows; a row missing one of them gets null in that position. Verified on two rows with no fields in common — {"alpha":1,"beta":2} and {"gamma":3,"delta":4}:

{"columns":["key","alpha","beta","gamma","delta"],
 "rows":[["d1",1,2,null,null],["d2",null,null,3,4]],"row_count":2}

The grammar

SELECT * | col [, col ...]
FROM route
WHERE or_term [OR or_term ...]
[ORDER BY col [ASC | DESC]]
[LIMIT n]

or_term  ::= pred [AND pred ...]
pred     ::= col OP literal
OP       ::= =  !=  <>  <  >  <=  >=

Three constraints that are easy to trip over, all confirmed against the live door:

WHERE is mandatory. SELECT key FROM "route" returns 400 UNSUPPORTED_SQL. So does … ORDER BY amount ASC without a WHERE. To select everything, use a predicate that is always true for your data, such as WHERE key >= '' or WHERE amount > 0.
LIMIT requires ORDER BY. SELECT key FROM "route" LIMIT 2 returns 400 UNSUPPORTED_SQL.
AND binds tighter than OR, and there are no parentheses. WHERE (status = 'paid') returns 400 UNSUPPORTED_SQL.

Not supported at all, each returning 400 UNSUPPORTED_SQL — all tested: JOIN, GROUP BY, aggregate functions such as COUNT(*), INSERT, parentheses, and dotted column names such as meta.region. For nested fields, aggregates or joins, use /v2/query.

There is no default row limit on this door. A 230-row route queried with WHERE key >= '' and no LIMIT returned all 230 rows ("row_count":230,"truncated":false). /v2/query would have capped the same request at 100. Bound your SELECT yourself.

Quote any route that contains a slash

An unquoted FROM identifier stops at the first character that is not a letter, digit, underscore or hyphen, so a slash ends the route name and the rest of the path is left over as text the parser cannot place. The result is a grammar error, not a route error:

# unquoted, five identical runs
-d '{"query":"SELECT * FROM _docs_probe/query/orders WHERE status = '"'"'paid'"'"'"}'
400  {"ok":false,"error":"unsupported SQL — v2 scope is SELECT *|col[,col...] FROM route
      WHERE or_term …","code":"UNSUPPORTED_SQL","scope":"select_cols_where"}

Wrap it in double quotes or backticks and it works. Both were verified:

SELECT * FROM "_docs_probe/query/orders" WHERE status = 'paid'
SELECT key, customer, amount FROM `_docs_probe/query/orders`
  WHERE amount > 50 ORDER BY amount DESC LIMIT 2

{"ok":true,"command":"SELECT","scope":"select_cols_where",
 "columns":["key","customer","amount"],
 "rows":[["o3","alice",300],["o5","bob",210]],
 "row_count":2,"scanned":5,"matched":4,"returned":2,
 "truncated":true,"complete":true,"time_ms":931}

A single-segment route works either way: SELECT key, n FROM _docs_probe WHERE n = 1 and the same query with backticks both returned 200 with "rows":[["p1",1]]. Quoting always works, so quote always.

A route name that parses cleanly but that your key may not read is where you get 403 FORBIDDEN — for example FROM nosuchroute, or a wildcard, which this door does not support: FROM `_docs_probe/query/*` returned 403 {"ok":false,"error":"forbidden","code":"FORBIDDEN"}.

Literals and types

String literals are single-quoted. Numbers are bare. The distinction is load-bearing, exactly as in /v2/query:

WHERE amount = '120'   200, "matched":0    (quoted literal vs numeric field)
WHERE amount = 120     200, "matched":1, rows [["o1"]]
WHERE status = paid    400 UNSUPPORTED_SQL (bare word is not a literal)
WHERE shipped = true   400 UNSUPPORTED_SQL
WHERE shipped = 'true' 200, rows [["o1",true],["o3",true],["o5",true]]

Booleans compare against the quoted literals 'true' and 'false', with = and != only. The entity key is available as the column key: WHERE key >= 'o3' returned o3, o4, o5.

AND/OR precedence, verified: WHERE status = 'open' AND amount > 50 OR amount >= 300 returned [["o3","paid",300],["o4","open",80]] — that is (status='open' AND amount>50) OR amount>=300.

Response fields

Field	Meaning
`command`	Always `"SELECT"`.
`scope`	`"select_cols_where"` on success and on `UNSUPPORTED_SQL`. Absent from `401` and `403` bodies.
`columns`	Column names, in the order the row arrays use.
`rows`	Arrays of values, one per matched row.
`row_count`	Length of `rows`. Equal to `returned`.
`scanned`, `matched`, `returned`	Same meanings as on `/v2/query`.
`truncated`, `complete`	Same meanings, including the unfolded-route `complete:false` behaviour.
`time_ms`	Server-side time.

There is no catalog_complete and no drops alias on this door.

Errors

Status	code	When
400	`UNKNOWN_PARAM`	Any URL query parameter. This door takes none.
400	`INVALID_JSON`	Body is not valid JSON (`"body is not valid JSON — nothing was applied"`) or not an object (`"body must be an object — nothing was applied"`).
400	`MISSING_FIELD`	No `query` field. `{}` returns `"body must include query — nothing was applied"`.
400	`UNKNOWN_FIELD`	Any body field other than `query`. `{"query":"…","limit":5}` is rejected — the limit belongs inside the SQL.
400	`INVALID_INPUT`	`query` is not a string. `{"query":5}` returned `"query must be a string — nothing was applied"`.
400	`UNSUPPORTED_SQL`	Anything outside the grammar, including an unquoted route containing a slash. The error text prints the whole grammar.
401	`KEY_REQUIRED`	No `X-Ocean-Key` header.
403	`FORBIDDEN`	The `FROM` route parsed, but your key may not read it. A route that does not exist lands here too.
503	`NOT_READY`	Declared, not observed in these probes. Storage backend not wired on the node that answered.

-d '{"query":"SELECT key FROM `_docs_probe/query/orders` LIMIT 2"}'
400
{"ok":false,
 "error":"unsupported SQL — v2 scope is SELECT *|col[,col...] FROM route WHERE or_term
          (OR or_term)* [ORDER BY col [ASC|DESC]] [LIMIT n]; or_term is pred (AND pred)*;
          pred is col OP lit; OP is = != <> < > <= >=; AND binds tighter than OR;
          LIMIT requires ORDER BY; no parens/JOIN (scope=select_cols_where)",
 "code":"UNSUPPORTED_SQL","scope":"select_cols_where"}

Payload shape: what is and is not queryable

Both endpoints see a drop's payload only if it is a JSON object. This was tested by writing four payload shapes to one route and reading them back through three doors.

What you POST as `payload`	Stored as	`GET /v2/entity/…`	`/v2/query`	Filterable?
`{"customer":"alice","amount":120}`	object	`{"customer":"alice","amount":120}`	`payload` object	yes
`"{\"customer\":\"alice\",\"amount\":120}"`	object — parsed at write	`{"customer":"alice","amount":120}`	`payload` object	yes
`"hello world"`	string	`"hello world"`	`payload: {}`	no
`"[1,2,3]"` or `[1,2,3]`	array	`[1,2,3]`	`payload: {}`	no

A payload written as a JSON string is parsed into an object at write time and queries normally. Row s1 was written with "payload":"{\"customer\":\"alice\",\"amount\":120}" and row s2 with the same content as a real object. Both came back identically, and {"filter":{"customer":"alice"}} matched both and nothing else:

{"ok":true,"scope":"squery.join_s1",
 "results":[{"key":"s1","ts":1787592072390,"payload":{"customer":"alice","amount":120}},
            {"key":"s2","ts":1787592072671,"payload":{"customer":"alice","amount":120}},
            {"key":"s3","ts":1787592101730,"payload":{}},
            {"key":"s4","ts":1787592102019,"payload":{}},
            {"key":"s5","ts":1787592102315,"payload":{}}],
 "scanned":5,"matched":5,"returned":5, …}

You do not need to stringify, and you gain nothing by it — send objects.

A payload that is not an object is stored and readable, but invisible to queries. Rows s3 ("hello world"), s4 ("[1,2,3]") and s5 ([1,2,3]) all read back correctly through the single-entity door:

GET /v2/entity/_docs_probe/query/strpay/s3  200  "hello world"
GET /v2/entity/_docs_probe/query/strpay/s5  200  [1,2,3]
GET /v2/list/_docs_probe/query/strpay       200  {"items":[…,{"key":"s3","value":"hello world"},
                                                              {"key":"s5","value":[1,2,3]}], …}

Through /v2/query, the same rows render as "payload":{}. They are still counted in scanned and, with no filter, in matched — so a count can include rows whose content you cannot see. Once a filter is present they never match: the same {"filter":{"customer":"alice"}} over all five rows returned "scanned":5,"matched":2. Through /v2/sql they appear with null in every payload column:

SELECT * FROM `_docs_probe/query/strpay` WHERE key >= 's1'
200
{"columns":["key","customer","amount"],
 "rows":[["s1","alice",120],["s2","alice",120],
         ["s3",null,null],["s4",null,null],["s5",null,null]],
 "row_count":5,"scanned":5,"matched":5,"returned":5, …}

The rule to build on: if you want a drop to be queryable, its payload must be a JSON object. Wrap scalars and arrays in one: {"value":"hello world"}, {"items":[1,2,3]}.

Choosing between the two

	`/v2/query`	`/v2/sql`
Route scope	One route, or `parent/*`	One route
Condition required	No — omit `filter` to list	Yes — `WHERE` is mandatory
OR	Not supported	Supported
Nested fields	One level (`meta.region`)	Not supported
Sort	One field	One column
Limit	Any time, default 100	Only with `ORDER BY`; no default cap
Aggregates	`$count $sum $avg $min $max`, with `group_by`	Not supported
Join	Left join on remote entity key	Not supported
Time window	`from`, `last`	Not supported
Result shape	Array of objects	`columns` + `rows` arrays

Things worth knowing before you build on this

Both doors read the current version only. Key o1 was rewritten twice during this page's measurements — 120 to 121 to 130 and back to 120. The route stayed at "scanned":5 throughout, and after each write exactly one row carried the new value with a new ts. Old versions are not extra rows and are not reachable from either endpoint.
Latency is measured in seconds, not milliseconds. Observed time_ms across the runs on this page: 0.9–1.7 s for filters and sorts over 5 rows on one exact route, ~2.0 s for a sorted top-5 over 230 rows, ~2.7 s for an aggregate over 230 rows, and 13.9–16.6 s for a wildcard over ~250 rows across 9 routes. Budget for that, and prefer exact routes over wildcards.
A missing route is a 403, not a 404. Do not use these endpoints to test whether a route exists.
No URL query parameters, on either door. Any parameter is 400 UNKNOWN_PARAM and the request does nothing.
Silent empty results are the failure mode to watch. A too-deep dotted path, a string literal against a numeric field, and a non-object payload all return 200 with "matched":0 and no warning. When a query returns nothing you expected rows for, check those three before anything else.

Base URL https://api2.infiniteocean.io · authenticate with the X-Ocean-Key header.