extension-querying-oql

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Querying OQL — quick reference

OQL 查询快速参考

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.

暴露OQL接口的canister提供两个只读方法：

方法	返回值	用途
`schema()`	一段JSON格式的 `Text`	canister的实体目录：包含每个实体的主键、字段和关联关系。
`execute(qJson : text)`	带类型的Candid `Result`	执行JSON编码的查询并返回匹配的行。

Calling the canister

调用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 '("<json-query>")' --query

execute

takes one

text

argument — the JSON query embedded as a Candid text literal. Wrap the JSON in

("...")

and escape every

\"

. 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

'\''

沙箱中已安装并配置好

icp

CLI；canister名称

backend

会解析为项目对应的canister（无需身份验证，无需canister ID）。两个方法均为

query

调用，因此每次调用都需使用

--query

参数：

bash

icp canister call backend schema '()' --query
icp canister call backend execute '("<json-query>")' --query

execute

接收一个

text

参数——嵌入为Candid文本字面量的JSON查询。将JSON包裹在

("...")

中，并将每个

转义为

\"

。例如查询

{"start":"customer","limit":3}

会变为：

bash

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

schema()

返回的JSON格式相同——是一个Candid文本字面量

("...转义后的json...")

；将

\"

转义回

（

\\

转义回

）即可读取内容。添加

--branch live

参数可读取已部署的canister而非草稿版本（live版本仅支持查询）。若字符串值包含单引号，需在shell中用

'\''

进行转义。

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).

获取schema并缓存。执行
```
icp canister call backend schema '()' --query
```
——将结果在会话中缓存；仅在canister部署时schema才会变更（第1节）。
将需求映射到实体。选择包含目标数据的实体。使用每个字段的
```
typeName
```
和
```
values
```
确定字面量类型，通过
```
role: {"edge": ...}
```
查看实体间的关联方式。
转换为一个或多个查询。从目标数据所在的实体开始（第2节）。添加
```
where
```
（第2.1节）、
```
orderBy
```
/
```
limit
```
/
```
offset
```
、
```
select
```
以及
```
aggregate
```
/
```
groupBy
```
（第2.2节）。在单个查询中使用点路径跨正向关联（第4.1节）；反向一对多关联需先获取父键，再使用
```
in
```
操作符（第4.2节）。
执行查询并读取结果。执行
```
icp canister call backend execute '("<json>")' --query
```
——通过单元格
```
name
```
解析Candid结果行（第3节）；若
```
hasMore
```
为真，使用
```
offset
```
进行分页（第5节）。
陷阱错误重试。无错误信封机制——重新读取schema，修正查询后重新执行（第6节）。

1. Discover —

schema

1. 发现实体——

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": "<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.

仅需获取一次并在会话中缓存——仅在canister部署时schema才会变更。

bash

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

按以下方式解读结果：

name
→ 实体名称；在查询中作为
```
start
```
的值使用。
primaryKey
→ 用于标识行的字段。关联关系
```
{"to": "<entity>"}
```
的值是目标实体中的主键值。
fields
→ 每个字段的
```
name
```
、标量
```
typeName
```
和
```
role
```
：
```
"payload"
```
（普通字段）或
```
{"edge": {"to": "<entity>"}}
```
（外键——用于遍历关联图）。当两列名称冲突时，名称可能带有
```
__1
```
、
```
__2
```
等后缀——需使用
```
schema()
```
返回的精确名称。
values
（可选）→ 字段可容纳的精确字面量（通常是变体的分支）。需使用这些字面量进行过滤，而非猜测值：例如
```
["free","pro","enterprise"]
```
意味着查询时需使用
```
"enterprise"
```
，而非
```
"Enterprise"
```
。若该字段不存在，则表示值无限制——若需要候选值，可通过查询获取样本。
typeName
→
```
value
```
对应的JSON字面量类型：
- ```
"Nat"
```
  → 无符号整数（
```
0
```
  、
```
1
```
  等）
- ```
"Int"
```
  → 有符号整数（
```
-1
```
  、
```
0
```
  、
```
1
```
  等）
- ```
"Float"
```
  → 带小数点的JSON数字（
```
0.5
```
  、
```
-3.14
```
  、
```
1.0e2
```
  ）。也接受纯整数（
```
10
```
  ）——数值类型可兼容，因此
```
gt(price, 10)
```
  会匹配
```
price : Float = 12.5
```
  的行。浮点数相等性基于IEEE-754位运算；对于
```
0.42
```
  这类无精确二进制形式的小数，需使用范围查询（
```
ge
```
  +
```
le
```
  ）。
- ```
"Bool"
```
  →
```
true
```
  /
```
false
```
- ```
"Text"
```
  → JSON字符串。
```
Principal
```
  字段会被标记为
```
"Text"
```
  （标准文本格式）——需使用字符串值进行过滤。

2. Form a query —

execute

2. 构建查询——

execute

A query is a single JSON object. Only

start

is required.

json

{
  "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.

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

查询是单个JSON对象。仅

start

为必填项。

json

{
  "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>", ...]
}

字段	默认值	说明
`start`	必填	来自 `schema()` 的实体 `name` 。
`where`	省略则无过滤	单个谓词（第2.1节）——不要包裹在 `{"filter": ...}` 中。
`groupBy`	`[]`	按这些字段对行进行分组；每个不同的组合对应一行输出（第2.2节）。
`aggregate`	`[]`	按分组进行聚合；若 `groupBy` 为空，则对所有过滤后的行进行聚合（第2.2节）。
`orderBy`	`[]` （canister定义的顺序，通常是插入顺序）	多键排序，第一个子句为首要排序条件。 `dir` 默认值为 `"asc"` 。
`offset`	`0`	跳过前N个匹配项。
`limit`	返回所有匹配项	最多保留N个结果。结果中的 `hasMore` 会告知是否存在更多结果。
`select`	所有非隐藏字段（聚合时为分组键+聚合列）	子集投影。

bash

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

过滤+排序+投影——核心结构（

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

2.1 谓词操作符

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:

bash

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).

Predicate

是一个仅包含一个键的JSON对象，该键为操作符名称。

操作符	格式	含义
`eq` / `ne` / `lt` / `le` / `gt` / `ge`	`{"<op>": { "field": "<name>", "value": <scalar> } }`	标量比较。
`in`	`{"in": { "field": "<name>", "value": [<scalar>, ...] } }`	成员判断；空数组不匹配任何项。
`contains` / `startsWith` / `endsWith`	`{"<op>": { "field": "<name>", "value": "<text>" } }`	对 `Text` 类型进行大小写敏感的子串/前缀/后缀匹配——在服务器端扫描，无需将行分页到上下文。
`icontains`	`{"icontains": { "field": "<name>", "value": "<text>" } }`	大小写不敏感的 `contains` 。推荐用于用户输入的搜索词。
`and` / `or` / `not`	`{"and": [<P>, ...]}` / `{"or": [<P>, ...]}` / `{"not": <P>}`	布尔逻辑组合。

文本搜索在服务器端执行——“名称包含north的客户”可通过单个查询实现，无需扫描所有行到上下文：

bash

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

<scalar>

必须匹配字段的

typeName

：

JSON值	映射类型	适用于typeName为以下的字段
`null`	`null_`	任何可空字段（在 `where` 中很少使用）
`true` / `false`	`bool`	`"Bool"`
`0` , `1` , `42`	`nat`	`"Nat"` （也可通过数值兼容匹配 `"Float"` ）
`-1` , `-42`	`int`	`"Int"` （也可通过数值兼容匹配 `"Float"` ）
`0.5` , `-3.14` , `1.0e2`	`float`	`"Float"`
`"foo"`	`text`	`"Text"`

字段值为

null_

的行将不满足所有比较操作，除了

ne

。使用

field

"<edge>"

和

value

= 目标实体的主键值进行关联过滤；或使用

"<edge>.<targetField>"

跨关联查询（第4.1节）。

2.2 Aggregate — count, groupBy, sum/avg/min/max

2.2 聚合——count、groupBy、sum/avg/min/max

Compute on the canister instead of fetching every row and tallying client-side.

fn

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

dept.budget

→

sum_dept_budget

aggregate

with no

groupBy

→ one row over the whole filtered set (

count

of an empty match is

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

在canister端计算，而非获取所有行后在客户端统计。

fn

可选值为

count

sum

avg

min

max

；除

count

外，其他函数均需指定

field

；

min

max

也适用于文本类型。

as

用于重命名输出列（默认值为

count

、

sum_<field>

等），且名称中不能包含

（点是关联遍历的分隔符——会导致解析错误）。若

field

为点路径，默认会用

连接各段（例如

dept.budget

的

sum

结果列名为

sum_dept_budget

）。无

groupBy

的

aggregate

会对所有过滤后的行生成一行结果（无匹配项时

count

结果为

）。无

aggregate

的

groupBy

相当于服务器端的DISTINCT操作。输出行仅包含分组键和聚合列。

“有多少个企业版客户？”——对过滤后的集合执行

count

，返回一行结果：

bash

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

“哪个客户经理的客户最多，且总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

3. 读取结果

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 { "<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.

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 };

外层

rows = vec { ... }

是行列表；每个内层

vec { ... }

代表一行。每个

record { value = variant { "<tag>" = <payload> }; name = "<field>" }

代表一个单元格——

name

标识字段，

<tag>

标识标量类型，

payload

是字段值。

35_000 : nat

中的下划线是数字分隔符——解析时需移除。

hasMore = false

表示已获取所有匹配项；

hasMore = true

表示结果被截断，需获取下一页。需通过

name

查找单元格，而非位置——若

select

变更，位置会发生变化。

4. Walk edges (joins)

4. 遍历关联（连接）

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).

正向（单值）关联可通过单个查询实现：点路径可跨越已声明的关联，适用于任何字段位置。反向（一对多）关联需通过两个查询实现，使用

in

模式（第4.2节）。

4.1 Forward (child → parent): dotted paths

4.1 正向（子→父）：点路径

"<edgeField>.<targetField>"

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.

"<edgeField>.<targetField>"

可在服务器端跨关联查询——适用于

where

、

groupBy

、

orderBy

、

aggregate.field

和

select

。在单个查询中跨关联投影：

bash

icp canister call backend execute '("{\"start\":\"customer\",\"where\":{\"eq\":{\"field\":\"companyName\",\"value\":\"Northstar Public\"}},\"select\":[\"companyName\",\"accountManager.name\",\"accountManager.office\"]}")' --query

多跳关联也支持（例如

"manager.department.name"

，最多4跳），且可与聚合组合——“按客户经理所在办公室统计平均收入”可通过一次调用实现：

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

规则：

首段必须是
```
schema()
```
中
```
role
```
为
```
{"edge": {"to": ... }}
```
的字段——点路径指向非关联字段会触发陷阱，即使其值看起来像外键（遍历由schema驱动，而非名称猜测）。若开发者未声明该关联，需退回到下文的双查询模式。
空值或无效外键会将整个点路径解析为
```
null
```
（左连接）：该行不满足所有比较操作，除了
```
ne
```
，且投影的单元格为
```
null
```
。
从多侧进行聚合。跨实体聚合基于起始实体的行：从
```
employee
```
实体对
```
"department.budget"
```
执行
```
avg
```
是按员工加权的平均值。如需按部门统计，需从
```
department
```
实体开始——或按点路径分组并聚合起始实体的字段。
选择关联字段本身（例如
```
"accountManager"
```
）仍会返回外键标量；不支持
```
.*
```
——需明确指定每个目标字段。

4.2 Reverse (one parent → many children)

4.2 反向（一父→多子）

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

单个父主键使用

eq

，批量父主键使用

in

——针对关联字段，值为目标实体的主键值。

bash

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

若父条件为普通谓词，无需批量查询——可通过正向关联过滤（第4.1节）。“所有由柏林办公室员工管理的客户”可通过单个查询实现：

bash

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

当父集合需要特定查询结构（Top-N、排序、分页）时，必须使用批量

in

模式：先收集主键，再在关联字段上使用

in

。“由三位最资深员工管理的客户”需两个查询：

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.


始终使用`in`进行批量查询，而非执行N次单独的`eq`查询。

4.3 Compound conditions

4.3 复合条件

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

可通过

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

4.4 双跳/自关联连接

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

当父键未给出需先查询时——例如“谁向项目

forge20

的负责人汇报？”——需执行两个查询。第二个查询通过自关联（

employee.manager

→

employee

）过滤，使用第一个查询返回的键：

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:

结果行的

lead

单元格是负责人的邮箱，例如priya@helix.systems——将其作为父键使用：

icp canister call backend execute '("{"start":"employee","where":{"eq":{"field":"manager","value":"priya@helix.systems"}},"select":["name","jobTitle","level"]}")' --query

---

icp canister call backend execute '("{"start":"employee","where":{"eq":{"field":"manager","value":"priya@helix.systems"}},"select":["name","jobTitle","level"]}")' --query

---

5. Pagination

5. 分页

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\":<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.

limit

用于限制结果数量。

hasMore

用于标识结果是否被截断。使用

offset

遍历分页：

text

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

始终显式设置

limit

。OQL本身无限制（省略

limit

会返回所有匹配项），但canister开发者可能会添加限制——此时超出限制的结果会被静默截断。

6. Pitfalls

6. 常见陷阱

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.

症状	原因/修复方案
`execute` 触发陷阱 `OQL: unknown entity '...'`	`start` 与 `schema()` 中的任何 `name` 都不匹配——实体名称区分大小写。重新读取schema。
`execute` 触发解析错误陷阱	JSON格式错误（ trailing逗号、单引号），或未正确转义为Candid文本字面量——需将JSON包裹在 `("...")` 中，并将每个内部 `"` 转义为 `\"` 。可先使用 `python3 -m json.tool` 验证JSON格式。
预期匹配的过滤条件未返回任何行	(1) `value` 字面量类型与字段的 `typeName` 不匹配（例如 `Nat` 类型字段使用 `"5"` ）；(2) `field` 拼写错误——未知字段会被静默处理为 `null_` ，因此大多数谓词会失败；(3) 存储中该字段确实为 `null_` 。
`gt` / `lt` 跨类型返回异常结果	混合类型比较未定义。确保两个操作数的 `typeName` 相同。
`contains` 未找到可见的行	`contains` / `startsWith` / `endsWith` 区分大小写。用户输入的搜索词请使用 `icontains` 。
点路径触发陷阱 `'x' is not an edge of 'y'`	首段不是已声明的关联——即使值看起来像外键，遍历仍由schema驱动。请改用双查询 `in` 模式。
跨实体平均值结果异常	聚合基于起始实体的行。需从要统计平均值的实体开始查询，或按点路径分组并聚合起始实体的字段。
`execute` 返回行但缺少字段	`select` 的字段不存在于实体中（拼写错误，或被开发者隐藏）。从 `select` 中移除该字段，或删除 `select` 使用默认投影。

无结构化错误信封。任何失败都会触发陷阱——修正查询后重试。

extension-querying-oql

Original

Translation

Querying OQL — quick reference

OQL 查询快速参考

Calling the canister

调用canister

Recipe

操作步骤

1. Discover —
`schema`

1. 发现实体——
`schema`

2. Form a query —
`execute`

2. 构建查询——
`execute`

2.1 Predicate operators

2.1 谓词操作符

2.2 Aggregate — count, groupBy, sum/avg/min/max

2.2 聚合——count、groupBy、sum/avg/min/max

3. Read the result

3. 读取结果

4. Walk edges (joins)

4. 遍历关联（连接）

4.1 Forward (child → parent): dotted paths

4.1 正向（子→父）：点路径

4.2 Reverse (one parent → many children)

4.2 反向（一父→多子）

collect the three emails from the rows, then:

从结果行中收集三个邮箱地址，然后执行：

4.3 Compound conditions

4.3 复合条件

4.4 Two-hop / self-edge join

4.4 双跳/自关联连接

the row's
`lead`
cell is the lead's email, e.g. priya@helix.systems — use it as the parent key:

结果行的
`lead`
单元格是负责人的邮箱，例如priya@helix.systems——将其作为父键使用：

5. Pagination

5. 分页

6. Pitfalls

6. 常见陷阱

extension-querying-oql

Original

Translation

Querying OQL — quick reference

OQL 查询快速参考

Calling the canister

调用canister

Recipe

操作步骤

1. Discover — schema

1. 发现实体——schema

2. Form a query — execute

2. 构建查询——execute

2.1 Predicate operators

2.1 谓词操作符

2.2 Aggregate — count, groupBy, sum/avg/min/max

2.2 聚合——count、groupBy、sum/avg/min/max

3. Read the result

3. 读取结果

4. Walk edges (joins)

4. 遍历关联（连接）

4.1 Forward (child → parent): dotted paths

4.1 正向（子→父）：点路径

4.2 Reverse (one parent → many children)

4.2 反向（一父→多子）

collect the three emails from the rows, then:

从结果行中收集三个邮箱地址，然后执行：

4.3 Compound conditions

4.3 复合条件

4.4 Two-hop / self-edge join

4.4 双跳/自关联连接

the row's lead cell is the lead's email, e.g. priya@helix.systems — use it as the parent key:

结果行的lead单元格是负责人的邮箱，例如priya@helix.systems——将其作为父键使用：

5. Pagination

5. 分页

6. Pitfalls

6. 常见陷阱

1. Discover —
`schema`

1. 发现实体——
`schema`

2. Form a query —
`execute`

2. 构建查询——
`execute`

the row's
`lead`
cell is the lead's email, e.g. priya@helix.systems — use it as the parent key:

结果行的
`lead`
单元格是负责人的邮箱，例如priya@helix.systems——将其作为父键使用：