Record Queries

A Record query returns a page of the Records of one Schema, shaped by an ordered select: each returned row is an object with one key per select entry, in select order. A select entry is a field of the Record, an aggregate (count, sum, avg, min or max) over the Records linked to it through a relationship, a sample of those linked Records (see Records), or an object that nests other entries (see Object).

With groupBy, the query is grouped (see Grouped Queries): each row is one group of the matching Records, and its select entries are group keys, aggregates of the group's Records, samples of them, and objects.

For how the query shape fits together and why it behaves as it does, see The Record Query Shape.

Input

Any other property is rejected.

Select Entries

Every entry has an id and an output alias. Within one select list (the query's select, or the select of an object or records entry), IDs must be unique and aliases must be unique, because the aliases are the keys of one object. Entries in different lists may share an ID or an alias. An alias must not be an integer such as 0 (integer keys would not keep select order), including an alias defaulted from a path such as tags.0, and must not be __proto__, constructor or prototype.

Field

A field returns the value at path of each Record.

path addresses a property of the Record's data as a property filter does: a dot-separated string or an array of segments, with array items addressed by index. * segments are not supported. The standard properties id, schemaId, createdAt, updatedAt, createdById and updatedById are also allowed. Dates of standard properties are returned as ISO 8601 strings. A missing value is returned as null. A path that does not exist in the Schema is rejected with BAD_REQUEST.

Aggregate

An aggregate summarises, for each returned Record, the Records linked to it through a relationship.

{
  "kind": "aggregate",
  "id": "releasedHours",
  "alias": "releasedHours",
  "fn": "sum",
  "path": "hours",
  "filter": { "type": "property", "path": "status", "operator": "eq", "value": "released" },
  "over": { "relationshipId": "<relationshipId>", "direction": "from" }
}

over is required outside a grouped query: an aggregate without over aggregates the Records of each group, so it needs groupBy. In a grouped query, over is not supported.

direction follows the relationship filter's fromFilter and toFilter:

For a relationship Item -> Release named In Release, querying Releases with direction: "from" counts each Release's Items, and querying Items with direction: "to" counts each Item's Releases. For a self-referencing relationship Task -> Task named Parent (the child is the from-Record and the parent is the to-Record), both directions are valid on Tasks: to counts each Task's parents and from counts its children.

The relationship must be in the queried Space and must have the queried Schema on the side opposite direction. Otherwise the query fails with NOT_FOUND.

Aggregate Functions

The type of the property at path, in the linked Schema, decides which functions apply and what they return.

Any other property type, such as text, boolean, object, array or relationship, and an unknown path are rejected with BAD_REQUEST, for example Aggregate "total": `sum` needs a number or duration property; "name" has type string. The path is checked only when the caller can read the linked Schema.

sum, avg, min and max skip stored values that are unset or are not of the property's type. A stored string "7" in a number property, a JSON number in a duration property, or a date property value that does not start with a YYYY-MM-DD date or is not a valid date, is ignored rather than failing the query. Durations must be ISO 8601 designator strings, may be negative (-PT1H), and are read to the microsecond: a sub-microsecond part is rounded, so PT0.0000005S counts as zero. A duration longer than about 178 million years is ignored.

Values are returned in the property's representation:

• Numbers are JSON numbers. A sum larger than the largest double (about 1.8e308) is returned as null.
• Durations: a sum or avg is an ISO 8601 duration, computed with the duration comparison rule (one month counts as 30 days, one day as 24 hours). It never overflows, however large the stored values are. For the exact arithmetic, see The Record Query Shape — Sums and Averages of Durations.
• min and max of a duration or a date return the stored value of the Record with the smallest or largest value, unchanged: P1M stays P1M, and a date-time keeps its offset or [zone]. Values that compare as equal, such as P1M and P30D, return the lowest stored text, so the result is deterministic. Durations are compared to the microsecond, so durations that differ only below a microsecond (PT0.0000001S and PT0.0000002S) are equal. Date-times compare as instants, and dates as calendar dates.
• min and max of createdAt or updatedAt are ISO 8601 UTC strings, as when these are selected as fields.

Object

An object entry returns a nested object with one key per entry of its own select, in select order. It only arranges the output: its entries are evaluated exactly as if they were selected next to it, for the same Record or group, and follow the same rules.

{
  "kind": "object",
  "id": "o1",
  "alias": "items",
  "select": [
    {
      "kind": "aggregate",
      "id": "a1",
      "alias": "released",
      "fn": "count",
      "filter": { "type": "property", "path": "status", "operator": "eq", "value": "released" },
      "over": { "relationshipId": "<relationshipId>", "direction": "from" }
    },
    { "kind": "field", "id": "t", "alias": "release", "path": "title" }
  ]
}

Records

A records entry returns a sample: an array of the first limit Records in sort order, each an object shaped by the entry's own select.

{
  "kind": "records",
  "id": "r1",
  "alias": "latestItems",
  "over": { "relationshipId": "<relationshipId>", "direction": "from" },
  "filter": { "type": "property", "path": "status", "operator": "eq", "value": "released" },
  "sort": [{ "orderBy": "createdAt", "order": "desc" }],
  "limit": 3,
  "select": ["id", "name"]
}

A sample is the first limit Records only. There is no cursor to read the rest of a sample.

The sample's select is checked against the sampled Schema: the related Schema with over, the queried Schema without. With over, it is checked only when the caller can read the related Schema. A path string in a sample's select is always a field, even in a grouped query where it equals a groupBy alias.

Group Key

A group entry returns the key of each group for one groupBy key. It is only allowed in a grouped query.

groupBy is the id of the groupBy key. In a grouped query, any other path string, and every field entry, is rejected outside a records entry's select: a group row has no single Record to read a field from.

Grouped Queries

A grouped query returns one row per group of the Records that match filter: one group for each distinct combination of the values of its groupBy keys.

Appointments per month, with how many of them are pending and the first three of them, nested in an appointments object:

{
  "spaceId": "<spaceId>",
  "schemaId": "<appointmentSchemaId>",
  "groupBy": [{ "id": "g1", "alias": "month", "path": "startsAt", "bucket": "month" }],
  "select": [
    "month",
    {
      "kind": "object",
      "id": "o1",
      "alias": "appointments",
      "select": [
        {
          "kind": "aggregate",
          "id": "a1",
          "alias": "pendingCount",
          "fn": "count",
          "filter": { "type": "property", "path": "status", "operator": "eq", "value": "pending" }
        },
        {
          "kind": "records",
          "id": "r1",
          "alias": "firstThree",
          "sort": [{ "orderBy": "startsAt", "order": "asc" }],
          "limit": 3,
          "select": ["name", "startsAt"]
        }
      ]
    }
  ]
}

{
  "rows": [
    {
      "month": "2026-01-01",
      "appointments": {
        "pendingCount": 5,
        "firstThree": [
          { "name": "Checkup", "startsAt": "2026-01-03T10:00:00Z" },
          { "name": "Physio", "startsAt": "2026-01-03T10:00:00Z" },
          { "name": "Eye exam", "startsAt": "2026-01-05T14:30:00Z" }
        ]
      }
    },
    {
      "month": "2026-02-01",
      "appointments": {
        "pendingCount": 1,
        "firstThree": [
          { "name": "Follow-up", "startsAt": "2026-02-02T09:00:00Z" },
          { "name": "X-ray", "startsAt": "2026-02-10T11:00:00+11:00[Australia/Sydney]" }
        ]
      }
    }
  ],
  "count": 2,
  "offset": 0,
  "limit": 25
}

Items per status and month:

{
  "spaceId": "<spaceId>",
  "schemaId": "<itemSchemaId>",
  "groupBy": [
    { "id": "g1", "path": "status" },
    { "id": "g2", "alias": "month", "path": "createdAt", "bucket": "month" }
  ],
  "select": [
    "status",
    "month",
    { "kind": "aggregate", "id": "a1", "alias": "items", "fn": "count" },
    { "kind": "aggregate", "id": "a2", "alias": "hours", "fn": "sum", "path": "hours" },
    { "kind": "aggregate", "id": "a3", "alias": "lastDue", "fn": "max", "path": "dueOn" }
  ]
}

{
  "rows": [
    { "status": "done", "month": "2024-01-01", "items": 4, "hours": 12.5, "lastDue": "2024-01-30" },
    { "status": "done", "month": "2024-02-01", "items": 1, "hours": 3, "lastDue": "2024-02-12" },
    { "status": "open", "month": "2024-02-01", "items": 2, "hours": 0, "lastDue": null }
  ],
  "count": 3,
  "offset": 0,
  "limit": 25
}

Group Keys

A key can be a property of these types. Values are read as filters and sorts read them. A value that is unset, or is not of the property's type, belongs to the null group, whose key is null.

id and schemaId, relationship fields (group by the relationship with over instead), and object, array, file, content and geojson properties are rejected with BAD_REQUEST, for example Group key "g1": cannot group by "tags", which has type array: group by a string, number, boolean, date or duration property.

With a bucket, a date groups by the calendar period it is in, and the key is the first day of the period as a YYYY-MM-DD date. Weeks start on Monday, and quarters on 1 January, 1 April, 1 July and 1 October. A date-time, including createdAt and updatedAt, is placed in a period in the viewer's timezone, the timezone in the user's regional preferences, which date filters also use. Only IANA timezone names, such as Australia/Melbourne, are used, with daylight saving time applied on each date. Aliases such as US/Eastern are accepted. When the caller has no timezone, or it is not an IANA timezone name (for example an offset such as +05:00), UTC is used. A date without time has no timezone and is bucketed as written.

Keys by Relationship

A key with over groups the Records by the Records they are linked to through a relationship, for example Assets by their Information Classification, or Tasks by the Tasks they depend on:

{
  "schemaId": "<task schema id>",
  "groupBy": [
    {
      "id": "g1",
      "alias": "dependsOn",
      "over": { "relationshipId": "<Depends On id>", "direction": "to" }
    }
  ],
  "select": ["dependsOn", { "kind": "aggregate", "id": "n", "alias": "tasks", "fn": "count" }]
}

• The key is the linked Record's ID. Each linked Record is one group.
• A Record linked to several Records is in the group of each, so the group counts and totals can add up to more than the number of matching Records. A Record linked to the same Record more than once is counted once in its group.
• A Record linked to no Record, or only to Records the caller cannot read, is in the null group. When the caller cannot read the related Schema, every Record is in the null group. Removed Records and removed links are ignored.
• direction names the side of the linked Records, as for an aggregate. Both directions of a self-referencing relationship are valid: for a Task → Task Depends On relationship, to groups each Task by the Tasks it depends on, and from by the Tasks that depend on it.
• A relationship that does not exist in the Space, or does not link the queried Schema on the other side of direction, is rejected with NOT_FOUND.
• bucket is not allowed. Groups are ordered by the linked Record's ID, with the null group last, unless sort orders them by a property of the linked Record (see Order and Paging of Groups).

Aggregates of a Group

An aggregate without over aggregates the Records of its group. path and filter address the queried Schema, and the functions in Aggregate Functions work as they do over a relationship: count counts the group's Records, and an aggregate's filter counts only some of them, as pendingCount does above. Its filter is checked against the caller's access at every relationship it crosses, as the query's filter is.

Samples of a Group

A records entry without over samples the Records of each group, as firstThree does above: the first limit of the group's Records in its sort order, read as the groups are. Its select, sort and filter address the queried Schema. Its filter narrows the sample only, and is checked against the caller's access at every relationship it crosses, as the query's filter is. The null group has a sample like every other group. A sample of a group is always an array, [] when no Record of the group matches its filter.

Order and Paging of Groups

sort orders the groups by group keys, aggregates and the linked Records of keys by relationship. Without groupBy in the sort key, each orderBy must be the path of a groupBy key, written as a dot-separated string, or the alias of an aggregate entry at the top level of select (not inside an object entry). A groupBy path takes precedence over an aggregate alias with the same name. A key by relationship's own ID order cannot be sorted by: it is always ascending by the linked Record's ID.

A sort key with groupBy set to the id of a key by relationship orders the groups by a property of each group's linked Record. orderBy is then a path in the related Schema: a string, number, boolean, date or duration property, or id, createdAt or updatedAt. For example, to list Opportunities by Stage in the Stages' Order:

{
  "schemaId": "<opportunity schema id>",
  "groupBy": [{ "id": "stage", "over": { "relationshipId": "<Stage id>", "direction": "from" } }],
  "select": ["stage", { "kind": "aggregate", "id": "n", "alias": "opportunities", "fn": "count" }],
  "sort": [{ "groupBy": "stage", "orderBy": "order", "order": "asc" }]
}

Values compare as they do in a Records sort. Groups whose linked Record has no value of the property's type, and the null group, sort last in either direction. When the caller cannot read the related Schema, every Record is in the null group, and orderBy is not checked. A path that is not such a property of a related Schema the caller can read is rejected with BAD_REQUEST, for example Cannot sort the groups of "stage" by "notes": a content property does not sort groups. groupBy in a sort key is rejected in a query without groupBy, and when it is not the id of a key by relationship. An aggregate sorts groups whose aggregate has no value (an avg, min or max over no values) last, in either direction, and compares values as Sorting by an Aggregate describes. After the requested keys, every other key is sorted ascending in groupBy order. Without sort, groups are sorted by every key ascending. A group's keys identify it, so the order is total and pages never repeat or skip a group. The null group sorts last ascending and first descending.

limit and offset page through the groups, and count is the number of groups. More groups exist when offset + rows.length < count. Samples are computed for the groups of the page only: each records entry reads the matching Records once more, but ranks and returns only the Records of the page's groups.

filter applies to the Records before they are grouped. A group with no matching Records does not exist, so it is not returned. There is no filter on the groups themselves.

Output

{
  "rows": [{ "title": "R1", "releasedItems": 2 }],
  "count": 5,
  "offset": 0,
  "limit": 25
}

Access

The queried Records are read exactly as when listing Records: the caller needs read access to the Schema, its role filters apply, and filter is checked against the caller's access at every relationship it crosses.

For each aggregate:

• Only live Records and links are aggregated; removed Records and removed links are not.
• The role filters of the linked Schema apply to the aggregated Records.
• The aggregate's filter is checked against the caller's access at every relationship it crosses, and its result never depends on data the caller cannot read. A part of the filter that cannot be evaluated, for example a relationship filter that crosses into a Schema the caller cannot read, is handled as in a filter on the queried Records: when it is the whole filter or is required by an and, the filter matches nothing and the aggregate has its value for no Records (0 for count and sum, null for avg, min and max); under or or not it is ignored.
• When the caller cannot read the linked Schema, the aggregate is null, and its path is not checked.

A records entry with over reads the linked Records as an aggregate does: live Records through live links, the linked Schema's role filters, and a filter checked against the caller's access at every relationship it crosses (when it cannot be evaluated, the sample is empty). When the caller cannot read the linked Schema, the whole entry is null, and its select and sort are not checked.

A grouped query groups exactly the Records the same query without groupBy would return: the Schema's role filters apply and filter is checked against the caller's access. Groups, their keys, their aggregates and their samples are computed from those Records only, so a count never includes a Record the caller cannot read, a sample never shows one, and a group whose Records are all hidden is not returned.

Limits

Entries nested in objects and records entries count towards the per-query limits.

filter always addresses properties of the queried Records. A filter path that is not a property and starts with an aggregate alias is rejected, because aggregates cannot filter the page. When a property has the same name as an aggregate alias, the path is the property.

Sorting by an Aggregate

In a query of Records, a sort entry whose orderBy is not a property of the queried Schema, but is the alias of an aggregate entry at the top level of select, sorts the Records by that aggregate. A property with the same name as an aggregate alias is always the property. An aggregate inside an object entry cannot sort the Records.

{
  "select": [
    "title",
    {
      "kind": "aggregate",
      "id": "openTasks",
      "alias": "openTasks",
      "fn": "count",
      "over": { "relationshipId": "<relationship-id>", "direction": "from" },
      "filter": { "type": "property", "path": "status", "operator": "eq", "value": "open" }
    }
  ],
  "sort": [
    { "orderBy": "openTasks", "order": "desc" },
    { "orderBy": "title", "order": "asc" }
  ]
}

• The aggregate is computed for every Record matching the filter before the page is chosen, so pages are slices of one order over all matching Records.
• It is read exactly as the returned aggregate is: only live linked Records the caller can read count, so Records the caller cannot read never change the order.
• Values compare as their type: counts, sums and averages of numbers as numbers, durations by length under the duration comparison rule, and dates and date-times as dates and instants.
• An aggregate with no value (an avg, min or max over no values, or any aggregate of a related Schema the caller cannot read) sorts last, in either direction. Ties are ordered by the next sort key, then by id.
• Sorting by an aggregate reads the links of every matching Record, so it costs more than sorting by a property on a large Schema. The query's statement timeout still applies: narrow the filter if it times out.

Errors