hap-cli-data-query

HAP Data Query Assistant (Filter · Pivot · Statistics)

Help users use the hap command line to retrieve the desired data from HAP worksheetsretrieve the desired data. The difficulty is not in the command itself, but inhow to write the parameter JSON correctly—how to write filter conditions, which comparison methods are supported in each place, and the dimensions and aggregations of pivots. This skill explains these error-prone points clearly and provides templates that can be used directly.

This skill only handles "query/filter/statistics" (read-only). After locating the target row, towrite data back(change remarks, change status, etc.), use hap worksheet record update——Writing records does not depend on the default app, with WORKSHEET_ID it can be located, -a/--app-id it may or may not be passed (passing it only limits the field search scope); when writing fails, do not think in terms of "is it because the app was not selected correctly". For what value each field type should pass, see hap guide record, and read back after writing to confirm. Adding, deleting, and modifying records also directly use the hap worksheet record related commands.

Covered commands

Command	Purpose	Filter format
`worksheet record list`	Query records by condition (filter/sort/paginate)	filter-json (unified filter syntax)
`worksheet record pivot`	Pivot aggregation (grouping dimensions + sum/count, etc.)	filter-json (same as above)
`worksheet record bottom-stats`	The summary at the bottom of the view (single-row statistics)	filter-controls (main site wire,different）
`worksheet record relations`	List the associated records along the association field of a certain record	None (directly provide record + field)
`worksheet record logs`	The change log of a certain record (who changed what and when)	None
`worksheet chart`	Chart command group (create/get/update/delete/list)	spec-json (on `chart create` )

Key understanding：record list and record pivot share the same set of filter-json；bottom-stats uses another old format, do not mix them. The vast majority of "query data" needs are satisfied by the first two.

Step 0 is always: get the field ID

In filter conditions, field can be a field ID (controlId), an alias, or the original column title displayed in the UI (names not present on the worksheet will be explicitly rejected); but the pivot dimension --rows-json / --columns-json and value --values-json only accept field IDs or aliases, writing a column title will be rejected. Query it first:

hap worksheet fields WORKSHEET_ID        # 列出每个字段的 controlId / 名称 / 类型

Note down the controlId of the fields to be filtered/grouped/aggregated, and use them directly in the JSON later.

filter-json: how to write the filter (common to record list / pivot)

Syntax

A filter condition is just a set of conditions, combined with logic ,can be nestedto express complex and/or:

{ "logic": "and",                     // and | or
  "items": [
    { "field": "<字段 ID、别名或列标题>",
      "op": "<比较方式>",
      "value": <标量 或 数组> },       // empty / not_empty 不需要 value
    { "logic": "or", "items": [ ... ] }  // 某一项本身也可以是一个组
  ] }

To express "(A and B) or (C and D)", nest another group inside items . This one syntax hap Collected everywhere — record queries, views, business rules, buttons, charts, workflow conditions — for the authoritative description see hap guide record filter(3.1 syntax, 3.2 which comparison methods are supported where, 3.3 pivot limitations); it follows the CLI version, so don't write from this skill's memory.

The old syntax ({"type":"group","children":[{"type":"condition",...}]} that tree) and the old spelling (notin、isempty、 ge、startswith…) are still available, and previously written filters still run; anything newly written should always use the form above.

Comparison methods: which ones record queries can use

record list / record pivot can use these 21：

eq ne  in not_in  contains not_contains  all_contains
starts_with not_starts_with  ends_with not_ends_with
gt gte lt lte  between not_between  belongs not_belongs
empty not_empty

Views, business rules, buttons, and charts have a few more dedicated comparison methods (date_is、self、rc_eq、array_eq…）， they do not exist on record list / record pivot does not exist— writing hap will be rejected on the spot and it will list what is available here. For the full comparison of each place see hap guide record filter section 3.2.

Key points:

Dates also use between(with ["2026-02-01","2026-03-31"]) and gt / gte / lt / lte. On views/rules/buttons/charts,hap will automatically be replaced with the date-specific comparison method based on the column type, so you can write it as usual; but date_between explicit date names like this are only legal in those places,record queries will reject it。
empty / not_empty Without value。
writing a wrong name, writing a comparison method that doesn't exist here, or writing a nonexistent column,hap will all be rejected by name before being sent, and it will tell you the available values.
node keys and the object form of value belong only to workflow conditions; writing them in a record query will be rejected.

🚨 The server does not report an error for an unrecognized comparison method — it discards the entire condition, returns the whole table, and reports success. Use a name it doesn't recognize to query twelve overdue orders, and what you get back is all orders, with no indication that the filter didn't take effect. The reason you can see an error is hap 's local interception before sending. Soas long as the filter request is not sent using hap (hand-rolled HTTP, another client), judge the result by the row count, not just by success。

How to fill value (by field type)

Option / single-select / multi-select fields: value uses the option's key is the most reliable (look it up from worksheet fields 's field options); writing the option's display text hap will also be converted to the key for you.
Relation fields (Relation): value uses the related record's rowid array, with in or eq. ⚠️ You must use rowid,you cannot use the related display title text(such as a version name) to match. For how to get this rowid see "Relation field filtering" below. The same applies to the reverse relation field of a subtable: fill value withthe parent record's rowid, and you can filter out all child rows of that parent record.
Collaborator fields (Collaborator): value uses the member's accountId array, with in/eq。
Department fields: value uses the department ID array, with belongs / not_belongs(on record pivot these two are only available forregionfields, see the pivot section below).
Text fields：contains / starts_with / eq etc., value is given directly as text.
Date field：between give ["2025-01-01","2025-01-31"], or gt / gte / lt / lte give a single date/timestamp (not date_between, record queries do not accept it).

Related field filtering: first get the rowid of the related record

Related fields (such as "Version", "Project", "Customer" in the "Task" table) inthe returned data look like this—an array, each item with sid(the rowid of the related record) and name(display title):

"版本字段": [ { "sid": "ITERATION_ROW_ID", "name": "迭代A" } ]

when filtering, value must use that sid。The most direct way is to have the command list them for you：

hap --json worksheet record relations <本表WS_ID> <本条记录rowid> <关联字段ID>

It follows this record's related fields to list the related records, along with their source worksheet information—saving the two manual reverse-lookup steps below. For pagination use -p/-n(default 20 per page), to also get system fields add --is-return-system-fields。

Two manual reverse-lookup methods (use when you don't yet have a specific record, or need to find by name):

Query from the related table: go to the related table record list --search "迭代A", get the target record's rowid. Note that the related display name comes from that table's title field and may differ from what you expect (for example the title is actually "2.3" rather than "Iteration A 2.3"), so go by what is actually queried.
Reverse-lookup from existing data: first record list pull a few records from this table, look at the {sid, name}that already appear in that related field, pick out the name matching sid。

After getting the sid, filter like this (in the Task table, "Version" relates to "Iteration A"):

hap worksheet record list TASK_WS_ID --filter-json '{
  "logic":"and",
  "items":[{"field":"<版本字段ID>","op":"in","value":["ITERATION_ROW_ID"]}]
}' -p 1 -n 100

SubTable: query "all child rows under a certain parent record"

SubTable data is not in the parent record; it is stored in a separate worksheet (the parent SubTable field's dataSource), child rows are attached back to the parent row throughthe reverse Relation field(the parent SubTable field's sourceField). So querying the child rows of a parent record = on the child table worksheet, filter by this reverse relation field = parent record rowid:

hap worksheet record list <子表WS_ID> --use-field-id-as-key -p 1 -n 100 \
  --filter-json '{"logic":"and","items":[
    {"field":"<反向关联字段ID>","op":"in","value":["<父记录rowid>"]}]}' \
  --sorts-json '[{"field":"<明细编号等排序字段ID>","isAsc":true}]'

<反向关联字段ID>: in the parent table worksheet fields , find the SubTable field's sourceField。
value isthe parent record's rowid(not the parent record's title text), use in。
A child row's "which row number" is determined by sorting, usually ascending by AutoNumber detail number, matching the order seen in the form; be sure to include --sorts-json, otherwise the default order is not guaranteed to be stable, and "counting the Nth row" will be wrong.

Complete example

"Surname Zhang, and joined in January"or"in Sales/Marketing, or belonging to North China region":

hap worksheet record list WORKSHEET_ID --filter-json '{
  "logic": "or",
  "items": [
    { "logic": "and", "items": [
      { "field": "name",         "op": "starts_with", "value": "张" },
      { "field": "onboard_date", "op": "between",     "value": ["2025-01-01","2025-01-31"] }
    ]},
    { "logic": "or", "items": [
      { "field": "dept_option",  "op": "in",      "value": ["k_sales","k_mkt"] },
      { "field": "dept_id",      "op": "belongs", "value": ["DEPT_HUABEI_ID"] }
    ]}
  ]
}'

Simple single condition (records with empty status)—note that empty takes no value:

hap worksheet record list WORKSHEET_ID --filter-json '{
  "logic":"and",
  "items":[{"field":"status","op":"empty"}]
}'

record list: query records

hap worksheet record list WORKSHEET_ID \
  --filter-json '<见上>' \
  --sorts-json '[{"field":"onboard_date","isAsc":false}]' \
  --fields '["name","status","amount"]' \   # 只返回这几个字段，省 token
  --page-size 50 --page-index 1 \
  --include-total-count                       # 想要总行数时加

--page-size / --page-index are all required。
--search keyword fuzzy search (across fields), can be combined with filter.
--view-id apply a view's built-in filter/sort.
If you don't pass --filter-json it queries everything (by pagination).
Returned data uses field aliases as keys by default(such as mingcheng、fuzeren、ssdd), not controlId. So when parsing results, get values by alias, or add --use-field-id-as-key to make keys become controlId. Aliases can be seen in worksheet fields .
--fields Passing a field ID or alias both work; use it when you only want certain columns to save tokens.
The member field returns an array of objects [{accountId, fullname, avatar, status}], take fullname to display the person's name.

record pivot: pivot aggregation (the first choice for statistics)

Use it for any "group by X, compute the total/count/average of Y".Only --values-json is required；--view-id is optional (if provided, the view's filters/sorting are applied; look up the view id with hap worksheet view list WORKSHEET_ID ), -p/-n if not provided, defaults are used.-a can also be omitted—the command will look up which app it belongs to based on the worksheet; if you pass it wrong, it will clearly tell you "This table does not belong to that app, please use the app it actually belongs to".

hap worksheet record pivot WORKSHEET_ID \
  --view-id VIEW_ID \                                          # 必填
  --rows-json '[{"field":"status"}]' \                         # 行维度（分组）
  --columns-json '[{"field":"create_date","granularity":3}]' \ # 列维度，可选
  --values-json '[{"field":"amount","aggregation":"SUM"}]' \   # 值（聚合），必填
  --filter-json '<同 list 的筛选条件>' \                        # 可选
  --include-summary                                            # 要总计行加

The return structure is data.pivot(an array), each item looks like:

{ "rows":    { "<行维度字段ID>": "进行中" },
  "columns": { },
  "values":  { "<值字段ID>": 190000.0 } }

When parsing, get the value from rows/values by field ID; if you want a ranking, sort the pivot array by some value and take the top N (the pivot itself does not guarantee sorting by value).

🚨 pivot supports fewer filters than list

The same filter-json,record list accepts,pivot may not accept—which comparisons pivot recognizes depends on what the field holds：

Comparison method	On pivot
`eq` / `ne`、`empty` / `not_empty`	all work
`contains` / `not_contains`	none work, rejected for any field type.`hap` is blocked locally with a prompt to switch to `starts_with` / `ends_with`, or simply switch to `record list`
`in` / `not_in`	only onsingle select, regionfields; text / number / date will be rejected
`gt` / `gte` / `lt` / `lte`、`between` / `not_between`	only onnumber, datefields
`belongs` / `not_belongs`	only onregionfields
`all_contains`	only ontextfields
`starts_with` / `ends_with` and its negations	only ontextfields

Except for contains , the remaining type-based restrictions hap cannot block (it doesn't know the field type); the server rejects them and adds the conditions you sent this time back into the error message, in the form "A pivot accepts fewer comparisons than record list does…」。

pivot also picks on the shape of value (（record list does not pick, both forms are accepted):

Comparison method	What pivot requires
`gt` / `gte` / `lt` / `lte`	bare value. Writing it as an array will be rejected—`"value": 0` works,`[0]` and `["0"]` both do not work
`between` / `not_between`	array of two elements. Writing it as a bare value, or giving only one element, will both be rejected
`eq` / `ne` / `in` / `not_in`	both are accepted, no need to worry

The same condition on record list works no matter which form is used,so this kind of error only shows up when switching to pivot. These two tables follow hap guide record filter 's 3.3.

If filtering doesn't work, fall back to record list to get the details, then aggregate locally—faster than wrestling with pivot's type and shape restrictions.

Dimensions (rows / columns)

Each item {"field":"<controlId 或别名>", "displayName":"可选", "granularity":<整数>, "includeEmpty":false}：

granularity only applies todate/regionField meaning:
- Date:1=day,2=week,3=month
- Region:1=province,2=province/city,3=province/city/county
includeEmpty: whether to also treat empty values as a group, default false.

Values

Each item {"field":"<controlId 或别名>", "aggregation":"<聚合>", "displayName":"可选"}：

aggregation(case-insensitive):COUNT COUNT,DISTINCTCOUNT DISTINCTCOUNT,SUM SUM,MIN MIN,MAX MAX,AVG AVG.
Just want to count rows(not targeting a specific field):field set the special value record_count, with COUNT。

Example — count orders and total amount for each status by month:

hap worksheet record pivot WORKSHEET_ID \
  --rows-json '[{"field":"create_date","granularity":3}]' \
  --columns-json '[{"field":"status"}]' \
  --values-json '[
    {"field":"record_count","aggregation":"COUNT"},
    {"field":"amount","aggregation":"SUM"}
  ]' --include-summary

bottom-stats and chart (secondary)

record bottom-stats: returns only the summary row at the bottom of the view (not a multi-dimensional pivot). It usesanother old format：--column-rpts '[{"controlId":"amount","rptType":1}]'(rptType is an integer, confirm the mapping by --help ),--filter-controls and uses the main-site wire structure rather than filter-json. There is also -k/--keywords to filter by keyword, and --report-id—if given, it readsa certain chart view's summary instead of the normal table summary (the chart id comes from hap worksheet chart list). When you need real grouped statistics, prefer record pivot。
record logs: the change log of a certain record, answering "who changed this value and when". Use it after locating a suspicious record; it is more precise than browsing the app-level hap app logs precise.
worksheet chart：is a command group（create / get / update / delete / list), create/modify chart configurations on a worksheet, not immediate data retrieval. To create a chart use hap worksheet chart create， --report-type(integer chart type) + -j/--spec-json(including xaxes/yaxisList/filter etc.) are both on thesubcommand, hap worksheet chart --help will only list subcommands. For how to write chart specs see hap guide chart. Creating charts is mostly "modifying the app", so it can be handed to hap-cli-app-editor; for pure data retrieval and analysis use record pivot is more direct.

First look at hap worksheet record bottom-stats --help / hap worksheet chart create --help then use it.

Key points for writing correct queries

Follow these rules and the vast majority of queries will work on the first try:

Field identifier: in filter conditions, field can use controlId, alias, or column title; in pivot dimensions / values only controlId or alias is recognized (use worksheet fields to check).
Filter conditions: unified syntax {"logic","items":[{"field","op","value"}]}, comparison methods use unified names like gte / lte / empty / not_in ; view/chart-specific ones (date_is、self...) are not accepted by record queries. If unsure, look at hap guide record filter。
value form: option fields use the option key; relation fields use the related record rowid; member fields use accountId;empty / not_empty has no value.
Required items: only record pivot 's --values-json is required. Pagination -p/-n both have default values (record list 20 per page,pivot 100 per page, page numbers all start from 1),--view-id both commands are optional. To fetch more at once, explicitly give -n。
Result parsing：By default, field aliases are used as keys. To use controlId as the key, add --use-field-id-as-key；Member/related fields are object arrays; take the fullname / name。
Shell escaping：Wrap the entire JSON in single quotes and use double quotes inside; when filtering is complex, write it to a file and then --filter-json "$(cat f.json)"。
verify the actual request：hap config log on before running the command, and you can see the actual request body sent in the logs.