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.6.1 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 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 typeName and values to choose literal types, and role: {"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 , 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). Run and read. icp canister call backend execute '("<json>")' --query — parse the Candid rows by cell name (§3); if hasMore , page with offset (§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 :