Grouping & Saved Views

Three ways to get more out of one list request: summarise it, reuse a stored configuration, and reach rows that have been deleted.

Grouping & aggregation

groupBy partitions records by a field's value; aggregate computes summary functions. Combined, they produce grouped roll-ups such as total amount per status.

GET /api/tables/orders/records?groupBy=status
GET /api/tables/orders/records?groupBy=region,status
GET /api/tables/orders/records?aggregate=amount:sum,amount:count,quantity:avg
GET /api/tables/orders/records?groupBy=status&aggregate=amount:sum

groupBy takes a comma-separated list of up to three fields, outermost first: ?groupBy=region,status partitions by region, then by status within each region. Every field named must exist on the table and be readable by the caller — a level naming a field you cannot read returns 404, exactly as a single unreadable field does, because a group header would otherwise report that field's distinct values.

Each aggregate entry is field:function — the field first, the function second. Supported functions are sum, count, avg, min and max.

A JSON form is also accepted: ?aggregate={"count":true,"sum":["amount"]}.

Without aggregate, groupBy returns groups: [{ name, path, count }]. With it, each group additionally carries aggregations, and the response still carries the whole-result-set aggregations at the top level — so one request answers "per group" and "overall" together rather than forcing a choice between them.

path holds the group's value at every level, outermost first, ending in its own name. It exists because a group's own value stops being a key the moment groupBy names more than one field: two regions can each hold a shipped group, and a count carrying only name cannot say which one it describes.

{
  "records": [/* … */],
  "groups": [
    // Level 1 — one entry per region.
    {
      "name": "EMEA",
      "path": ["EMEA"],
      "count": 42,
      "aggregations": { "sum": { "amount": 5100 } },
    },
    {
      "name": "AMER",
      "path": ["AMER"],
      "count": 18,
      "aggregations": { "sum": { "amount": 2300 } },
    },
    // Level 2 — one entry per status WITHIN each region.
    {
      "name": "shipped",
      "path": ["EMEA", "shipped"],
      "count": 30,
      "aggregations": { "sum": { "amount": 4200 } },
    },
    {
      "name": "pending",
      "path": ["EMEA", "pending"],
      "count": 12,
      "aggregations": { "sum": { "amount": 900 } },
    },
    {
      "name": "shipped",
      "path": ["AMER", "shipped"],
      "count": 18,
      "aggregations": { "sum": { "amount": 2300 } },
    },
  ],
  "aggregations": { "sum": { "amount": 7400 } },
}

groups carries one entry per group at every named level, in a flat array ordered by depth — every level-1 group first, then every level-2 group, and so on. It is not a tree: to build one, bucket the entries by path.length and match each entry to its parent on path.slice(0, -1). A single-field groupBy returns single-entry paths, so a reader that keys on name alone keeps working unchanged.

The figures reconcile down the levels: the level-2 groups of one parent sum to that parent, and the level-1 groups sum to the top-level aggregations.

Every count and every aggregation describes the whole filtered result set, not the page returned alongside it — the figures do not move when you page through the same query, at any level. A group whose rows all fall on a later page still appears in groups.

Saved views

A view bundles a filter tree and a sort order under a stable id, so a recurring query is one parameter rather than a re-encoded expression on every request.

GET /api/tables/tasks/records?view=2

?view= matches on either the view's id or its name. A value matching neither — or any view reference against a table declaring no views — returns 404 NOT_FOUND with "View '<x>' not found".

tables:
  - id: 1
    name: tasks
    views:
      - id: 2
        name: Active Tasks
        filters:
          and:
            - field: status
              operator: in
              value: [todo, in_progress]
        sorts:
          - field: priority
            direction: desc

Two rules govern how explicit parameters interact with the view, and they differ:

Including deleted records

Soft-deleted rows are excluded by default. Two parameters reach them:

includeDeleted is compared against the exact string true; any other value, including only, is treated as "exclude deleted". Trash-only is ?deleted=true, or the dedicated GET /api/tables/:tableId/trash endpoint.

GET /api/tables/tasks/records?includeDeleted=true
GET /api/tables/tasks/records?deleted=true

See Soft Delete & Restore for the full recovery workflow.

Related pages

• Filtering, Sorting & Pagination — the rest of the list query grammar.
• Table Views — declaring saved filter/sort/field configurations.
• Soft Delete & Restore — trash, restore, and cascades.
• Records Overview — list envelope and cross-cutting rules.
• Analytics — pre-built aggregates over usage rather than records.