Querying OQL — quick reference
An OQL canister exposes two read-only methods:
Calling the canister
The icp CLI is already installed and configured in the sandbox; the
canister name backend resolves to the project's canister (no identity,
no canister ID). Both methods are query calls, so every invocation
uses --query:
execute takes one text argument — the JSON query embedded as a
Candid text literal. Wrap the JSON in ("...") and escape every " as
\". The query {"start":"customer","limit":3} becomes:
schema() returns its JSON the same way — a Candid text literal
("...escaped json..."); unescape \" → " (and \\ → \) to read
it. Add --branch live to read the deployed canister instead of the
draft (live is query-only). If a string value contains a single quote,
escape it for the shell with '\''.
Recipe
- Get the schema once.
icp canister call backend schema '()' --query— cache it for the session; it changes only between deployments (§1). - Map the request to entities. Pick the entity that holds the answer. Use each field's
typeNameandvaluesto choose literal types, androle: {"edge": ...}to see how entities connect. - Translate into one or more queries. Start from the entity whose rows you want (§2). Add
where(§2.1),orderBy/limit/offset,select, andaggregate/groupBy(§2.2). Cross a forward edge with a dotted path in a single query (§4.1); a reverse one-to-many needs the parent keys first, thenin(§4.2). - Run and read.
icp canister call backend execute '("<json>")' --query— parse the Candid rows by cellname(§3); ifhasMore, page withoffset(§5). - Retry on traps. There is no error envelope — re-read the schema, fix the query, rerun (§6).
1. Discover — schema
Fetch once and cache for the session — it only changes between canister deployments.
Read it like this:
name→ entity name; use it asstartin queries.primaryKey→ field whose value identifies a row. An edge{"to": "<entity>"}value is a primary-key value in that target.fields→ each field'sname, scalartypeName, androle:"payload"(plain field) or{"edge": {"to": "<entity>"}}(a foreign key — how you traverse the graph). Names may carry a__1,__2, … suffix when two columns would share a name — use the exact namesschema()reports.values(optional) → the exact literals a field can hold (typically a variant's arms). Filter with those literals, not guesses:["free","pro","enterprise"]means query"enterprise", not"Enterprise". Absent ⇒ unbounded — sample it with a query if you need candidates.typeName→ JSON literal type forvalue:"Nat"→ unsigned integer (0,1, …)"Int"→ signed integer (-1,0,1, …)"Float"→ JSON number with a decimal point (0.5,-3.14,1.0e2). A bare integer (10) is also accepted — numeric variants bridge, sogt(price, 10)matches aprice : Float = 12.5row. Float equality is bitwise IEEE-754; use a range (ge+le) for decimals like0.42with no exact binary form."Bool"→true/false"Text"→ JSON string.Principalfields report as"Text"(canonical textual form) — filter them with a string value.
2. Form a query — execute
A query is a single JSON object. Only start is required.
Filter + sort + project — the core shape (where + orderBy + limit + select):
2.1 Predicate operators
A Predicate is a JSON object with exactly one key that names the
operator.
Text search runs server-side — "the customer whose name mentions north" is one query, not a row scan into context:
<scalar> must match the field's typeName:
A row whose field is null_ fails every relation except ne. Filter
by relationship with field = "<edge>" and value = the target
entity's primary-key value; or read through an edge with
"<edge>.<targetField>" (§4.1).
2.2 Aggregate — count, groupBy, sum/avg/min/max
Compute on the canister instead of fetching every row and tallying
client-side. fn is count/sum/avg/min/max; field is required
for every fn except count; min/max also work on text. as renames
the output column (default count, sum_<field>, …) and must not
contain . (dots are the edge-traversal separator — parse error). For a
dotted field the default joins segments with _ (sum of
dept.budget → sum_dept_budget). aggregate with no groupBy → one
row over the whole filtered set (count of an empty match is 0).
groupBy with no aggregate → a server-side DISTINCT. Output rows
contain only the group-key + aggregate columns.
"How many enterprise customers?" — count over a filtered set, one row out:
"Which account manager has the most customers, and total MRR?" — groupBy + count + sum:
3. Read the result
The outer rows = vec { ... } is the row list; each inner vec { ... }
is one row. Each record { value = variant { "<tag>" = <payload> }; name = "<field>" }
is one cell — name tells you which field, the <tag> tells you the
scalar type, the payload is the value. 35_000 : nat underscores are
digit separators — strip them if parsing. hasMore = false ⇒ you got
every match; hasMore = true ⇒ truncated, fetch the next page. Look
cells up by name, not position — order shifts if select changes.
4. Walk edges (joins)
Forward (single-valued) relationships are one query: a dotted path
crosses a declared edge, in any field position. Reverse (one-to-many)
relationships stay two queries with the in pattern (§4.2).
4.1 Forward (child → parent): dotted paths
"<edgeField>.<targetField>" reads through the edge server-side — in
where, groupBy, orderBy, aggregate.field, and select. Project
through an edge in one query:
Multi-hop chains work ("manager.department.name", max 4 hops), and it
composes with aggregation — "average revenue by the account manager's
office" is one call:
Rules:
- The head segment must be a field whose
roleis{"edge": {"to": ... }}inschema()— a dotted path into a non-edge field traps, even if its values look like foreign keys (traversal is schema-driven, not name-guessed). If the author didn't declare the edge, fall back to the two-query pattern below. - A null or dangling FK resolves the whole dotted path to
null(left-join): the row fails every relation exceptne, and projects the cell asnull. - Aggregate from the many side. Cross-entity aggregates run over the
start entity's rows:
avgof"department.budget"fromemployeeis employee-weighted. For per-department numbers, start fromdepartment— or group by the dotted path and aggregate start-entity fields. - Selecting the bare edge field (
"accountManager") still returns the FK scalar; there is no.*— name each target field you want.
4.2 Reverse (one parent → many children)
eq for one parent primary key, in for a batch — on the edge field,
with the target entity's primary-key values.
When the parent condition is a plain predicate, you don't need the batch — it's a forward filter through the edge (§4.1). "All customers managed by anyone in the Berlin office" is one query:
The batch in pattern is required when the parent set needs its own
query shape (top-N, ordered, paginated): collect the keys first, then
in on the edge field. "Customers managed by the three most senior
employees" is two queries:
Always batch with in rather than running N separate eq queries.
4.3 Compound conditions
Stack with and / or:
4.4 Two-hop / self-edge join
When the parent key isn't given but must be looked up first — e.g. "who
reports to the lead of project forge20?" — run two queries. The second
filters on a self-edge (employee.manager → employee) by the key the
first query returned:
5. Pagination
limit caps results. hasMore reports truncation. Walk pages with
offset:
Always set limit explicitly. OQL itself imposes no cap (omitting
limit returns every match), and a canister author may add one — in
which case over-asking is silently truncated.
6. Pitfalls
There is no structured error envelope. Any failure is a trap — fix the query and retry.
