extension-querying-oql

name	extension-querying-oql
description	Quick reference for the Caffeine Data Intelligence agent to query an OQL-exposing canister (schema() + execute()) through the `icp` CLI against the project's `backend` canister: read the schema, form JSON queries (filter / order / paginate / aggregate / dotted-path edges), and parse the Candid result rows.
version	0.4.0
compatibility	{"mops":{},"npm":{}}
caffeineai-subscription	["none"]

Querying OQL — quick reference

An OQL canister exposes two read-only methods:

Method	Returns	Purpose
schema()	one JSON Text	Catalogue of the canister's entities: each entity's primary key, fields, and edges.
execute(qJson : text)	typed Candid Result	Runs a JSON-encoded query and returns matching rows.

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:

icp canister call backend schema '()' --query
icp canister call backend execute '("<json-query>")' --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:

icp canister call backend execute '("{\"start\":\"customer\",\"limit\":3}")' --query

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

1. Get the schema once. icp canister call backend schema '()' --query — cache it for the session; it changes only between deployments (§1).
2. Map the request to entities. Pick the entity that holds the answer. Use each field's typeName and values to choose literal types, and role: {"edge": ...} to see how entities connect.
3. Translate into one or more queries. Start from the entity whose rows you want (§2). Add where (§2.1), orderBy / limit / offset, select, and aggregate / 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, then in (§4.2).
4. Run and read. icp canister call backend execute '("<json>")' --query — parse the Candid rows by cell name (§3); if hasMore, page with offset (§5).
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.

icp canister call backend schema '()' --query

Read it like this:

• name → entity name; use it as start in queries.
• primaryKey → field whose value identifies a row. An edge {"to": "<entity>"} value is a primary-key value in that target.
• fields → each field's name, scalar typeName, and role: "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 names schema() 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 for value:
  • "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, so gt(price, 10) matches a price : Float = 12.5 row. Float equality is bitwise IEEE-754; use a range (ge + le) for decimals like 0.42 with no exact binary form.
  • "Bool" → true / false
  • "Text" → JSON string. Principal fields 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.

{
  "start":     "<entityName>",
  "where":     <Predicate>,
  "groupBy":   ["<fieldName>", ...],
  "aggregate": [{ "fn": "count|sum|avg|min|max", "field": "<fieldName>", "as": "<outName>" }, ...],
  "orderBy":   [{ "field": "<fieldName>", "dir": "asc|desc" }, ...],
  "offset":    <Nat>,
  "limit":     <Nat>,
  "select":    ["<fieldName>", ...]
}

Field	Default	Notes
start	(required)	An entity name from schema().
where	omit ⇒ no filter	A single predicate (§2.1) — not wrapped in {"filter": ...}.
groupBy	[]	Bucket rows by these fields; one output row per distinct combination (§2.2).
aggregate	[]	Aggregates per bucket, or over all rows when groupBy is empty (§2.2).
orderBy	[] (canister-defined order, typically insertion order)	Multi-key sort, first clause primary. dir defaults "asc".
offset	0	Drop the first N matches.
limit	every match	Keep at most N. hasMore in the result tells you if more exist.
select	every non-hidden field (or, when aggregating, group-key + aggregate columns)	Subset projection.

icp canister call backend execute '("{\"start\":\"customer\",\"limit\":3}")' --query

Filter + sort + project — the core shape (where + orderBy + limit + select):

icp canister call backend execute '("{\"start\":\"customer\",\"where\":{\"eq\":{\"field\":\"plan\",\"value\":\"enterprise\"}},\"orderBy\":[{\"field\":\"monthlyRevenueUsd\",\"dir\":\"desc\"}],\"limit\":5,\"select\":[\"companyName\",\"monthlyRevenueUsd\",\"accountManagerName\"]}")' --query

2.1 Predicate operators

A Predicate is a JSON object with exactly one key that names the operator.

Operator	Shape	Meaning
eq / ne / lt / le / gt / ge	{"<op>": { "field": "<name>", "value": <scalar> } }	Scalar relation.
in	{"in": { "field": "<name>", "value": [<scalar>, ...] } }	Membership; empty array matches nothing.
contains / startsWith / endsWith	{"<op>": { "field": "<name>", "value": "<text>" } }	Case-sensitive substring / prefix / suffix on Text — server-side scan, no need to page rows into context.
icontains	{"icontains": { "field": "<name>", "value": "<text>" } }	Case-insensitive contains. Prefer this for user-typed search terms.
and / or / not	{"and": [<P>, ...]} / {"or": [<P>, ...]} / {"not": <P>}	Boolean composition.

Text search runs server-side — "the customer whose name mentions north" is one query, not a row scan into context:

icp canister call backend execute '("{\"start\":\"customer\",\"where\":{\"icontains\":{\"field\":\"companyName\",\"value\":\"north\"}},\"select\":[\"companyName\",\"accountManagerName\"]}")' --query

<scalar> must match the field's typeName:

JSON	Maps to	Use for fields with typeName
null	null_	any nullable field (rare in where)
true / false	bool	"Bool"
0, 1, 42	nat	"Nat" (also matches "Float" via numeric bridging)
-1, -42	int	"Int" (also matches "Float" via numeric bridging)
0.5, -3.14, 1.0e2	float	"Float"
"foo"	text	"Text"

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:

icp canister call backend execute '("{\"start\":\"customer\",\"where\":{\"eq\":{\"field\":\"plan\",\"value\":\"enterprise\"}},\"aggregate\":[{\"fn\":\"count\"}]}")' --query

"Which account manager has the most customers, and total MRR?" — groupBy + count + sum:

icp canister call backend execute '("{\"start\":\"customer\",\"groupBy\":[\"accountManager\"],\"aggregate\":[{\"fn\":\"count\"},{\"fn\":\"sum\",\"field\":\"monthlyRevenueUsd\",\"as\":\"mrr\"}],\"orderBy\":[{\"field\":\"count\",\"dir\":\"desc\"}],\"limit\":1}")' --query

---

3. Read the result

type Value  = variant { null_; bool : bool; nat : nat; int : int; float : float; text : text };
type Cell   = record { name : text; value : Value };
type Result = record { rows : vec vec Cell; hasMore : bool };

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:

icp canister call backend execute '("{\"start\":\"customer\",\"where\":{\"eq\":{\"field\":\"companyName\",\"value\":\"Northstar Public\"}},\"select\":[\"companyName\",\"accountManager.name\",\"accountManager.office\"]}")' --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:

icp canister call backend execute '("{\"start\":\"customer\",\"groupBy\":[\"accountManager.office\"],\"aggregate\":[{\"fn\":\"avg\",\"field\":\"monthlyRevenueUsd\",\"as\":\"avg_mrr\"}],\"orderBy\":[{\"field\":\"avg_mrr\",\"dir\":\"desc\"}]}")' --query

Rules:

• The head segment must be a field whose role is {"edge": {"to": ... }} in schema() — 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 except ne, and projects the cell as null.
• Aggregate from the many side. Cross-entity aggregates run over the start entity's rows: avg of "department.budget" from employee is employee-weighted. For per-department numbers, start from department — 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.

icp canister call backend execute '("{\"start\":\"customer\",\"where\":{\"eq\":{\"field\":\"accountManager\",\"value\":\"[e-mail]\"}},\"select\":[\"companyName\",\"monthlyRevenueUsd\"]}")' --query

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:

icp canister call backend execute '("{\"start\":\"customer\",\"where\":{\"eq\":{\"field\":\"accountManager.office\",\"value\":\"Berlin\"}},\"select\":[\"companyName\",\"monthlyRevenueUsd\"]}")' --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:

icp canister call backend execute '("{\"start\":\"employee\",\"orderBy\":[{\"field\":\"level\",\"dir\":\"desc\"}],\"limit\":3,\"select\":[\"email\"]}")' --query
# collect the three emails from the rows, then:
icp canister call backend execute '("{\"start\":\"customer\",\"where\":{\"in\":{\"field\":\"accountManager\",\"value\":[\"[e-mail]\",\"[e-mail]\",\"[e-mail]\"]}},\"select\":[\"companyName\",\"monthlyRevenueUsd\"]}")' --query

Always batch with in rather than running N separate eq queries.

4.3 Compound conditions

Stack with and / or:

icp canister call backend execute '("{\"start\":\"customer\",\"where\":{\"and\":[{\"eq\":{\"field\":\"plan\",\"value\":\"enterprise\"}},{\"in\":{\"field\":\"country\",\"value\":[\"US\",\"CA\",\"DE\"]}},{\"ge\":{\"field\":\"monthlyRevenueUsd\",\"value\":20000}}]},\"orderBy\":[{\"field\":\"monthlyRevenueUsd\",\"dir\":\"desc\"}]}")' --query

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:

icp canister call backend execute '("{\"start\":\"project\",\"where\":{\"eq\":{\"field\":\"codename\",\"value\":\"forge20\"}},\"select\":[\"lead\"]}")' --query
# the row's `lead` cell is the lead's email, e.g. [e-mail] — use it as the parent key:
icp canister call backend execute '("{\"start\":\"employee\",\"where\":{\"eq\":{\"field\":\"manager\",\"value\":\"[e-mail]\"}},\"select\":[\"name\",\"jobTitle\",\"level\"]}")' --query

---

5. Pagination

limit caps results. hasMore reports truncation. Walk pages with offset:

offset = 0
limit  = 25
loop:
  result = icp canister call backend execute '("{\"start\":\"...\",\"limit\":25,\"offset\":<offset>,...}")' --query
  consume result.rows
  if not result.hasMore: break
  offset += limit

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

Symptom	Cause / Fix
execute traps OQL: unknown entity '...'	start doesn't match any name from schema() — entity names are case-sensitive. Re-read the schema.
execute traps with a parse error	The JSON was malformed (trailing comma, single quotes), or not escaped as a Candid text literal — wrap as ("...") with every inner " escaped as \". Validate the JSON with python3 -m json.tool first.
No rows returned for a filter you expect to match	(1) value literal type doesn't match the field's typeName ("5" for a Nat); (2) typo in field — unknown fields are silently null_, so most predicates fail; (3) the field is genuinely null_ in storage.
gt / lt returns weird results across types	Mixed-type comparisons aren't defined. Make sure both operands are the same typeName.
contains misses rows you can see	contains / startsWith / endsWith are case-sensitive. Use icontains for user-typed search terms.
Dotted path traps 'x' is not an edge of 'y'	The head segment isn't a declared edge — traversal is schema-driven even when values look like FKs. Use the two-query in pattern instead.
Cross-entity average looks wrong	Aggregates run over the start entity's rows. Start from the entity whose rows you want averaged, or group by the dotted path and aggregate start-entity fields.
execute returns rows but missing fields	A field you select-ed isn't in the entity (typo, or hidden by the author). Drop it from select, or remove select for the default projection.

There is no structured error envelope. Any failure is a trap — fix the query and retry.