--- 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`: ```bash icp canister call backend schema '()' --query icp canister call backend execute '("")' --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: ```bash 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 '("")' --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. ```bash 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": ""}` value is a primary-key value in that target. - **`fields`** → each field's `name`, scalar `typeName`, and `role`: `"payload"` (plain field) or `{"edge": {"to": ""}}` (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. ```json { "start": "", "where": , "groupBy": ["", ...], "aggregate": [{ "fn": "count|sum|avg|min|max", "field": "", "as": "" }, ...], "orderBy": [{ "field": "", "dir": "asc|desc" }, ...], "offset": , "limit": , "select": ["", ...] } ``` | 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. | ```bash icp canister call backend execute '("{\"start\":\"customer\",\"limit\":3}")' --query ``` Filter + sort + project — the core shape (`where` + `orderBy` + `limit` + `select`): ```bash 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` | `{"": { "field": "", "value": } }` | Scalar relation. | | `in` | `{"in": { "field": "", "value": [, ...] } }` | Membership; empty array matches nothing. | | `contains` / `startsWith` / `endsWith` | `{"": { "field": "", "value": "" } }` | Case-sensitive substring / prefix / suffix on `Text` — server-side scan, no need to page rows into context. | | `icontains` | `{"icontains": { "field": "", "value": "" } }` | Case-insensitive `contains`. Prefer this for user-typed search terms. | | `and` / `or` / `not` | `{"and": [

, ...]}` / `{"or": [

, ...]}` / `{"not":

}` | Boolean composition. | Text search runs server-side — "the customer whose name mentions north" is one query, not a row scan into context: ```bash icp canister call backend execute '("{\"start\":\"customer\",\"where\":{\"icontains\":{\"field\":\"companyName\",\"value\":\"north\"}},\"select\":[\"companyName\",\"accountManagerName\"]}")' --query ``` `` 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` = `""` and `value` = the target entity's **primary-key value**; or read *through* an edge with `"."` (§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_`, …) 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: ```bash 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`: ```bash 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 ```candid 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 { "" = }; name = "" }` is one cell — `name` tells you which field, the `` 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 `"."` reads through the edge server-side — in `where`, `groupBy`, `orderBy`, `aggregate.field`, and `select`. Project through an edge in one query: ```bash 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: ```bash 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. ```bash icp canister call backend execute '("{\"start\":\"customer\",\"where\":{\"eq\":{\"field\":\"accountManager\",\"value\":\"daniel@helix.systems\"}},\"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: ```bash 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: ```bash 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\":[\"alex@helix.systems\",\"james@helix.systems\",\"sarah@helix.systems\"]}},\"select\":[\"companyName\",\"monthlyRevenueUsd\"]}")' --query ``` Always batch with `in` rather than running N separate `eq` queries. ### 4.3 Compound conditions Stack with `and` / `or`: ```bash 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: ```bash 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. priya@helix.systems — use it as the parent key: icp canister call backend execute '("{\"start\":\"employee\",\"where\":{\"eq\":{\"field\":\"manager\",\"value\":\"priya@helix.systems\"}},\"select\":[\"name\",\"jobTitle\",\"level\"]}")' --query ``` --- ## 5. Pagination `limit` caps results. `hasMore` reports truncation. Walk pages with `offset`: ```text offset = 0 limit = 25 loop: result = icp canister call backend execute '("{\"start\":\"...\",\"limit\":25,\"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.