Grouping & aggregation

Set groupBy and/or aggregate and the compiler switches into aggregate mode. The output is row-shaped: [{ <groupKey>: ..., <agg>: ... }, ...] when grouped, or a single-row [{ <agg>: ... }] when aggregating over the whole set.

These examples use the same entities dataset as Filtering (stepbook q analyze …).

# Count entities of each type that have a positive score.
from: entities
where: { field: score, gt: 0 }
groupBy: type
aggregate: count

$each(((analyze.entities)[score > 0]){type: { "count": $count($) }}, function($v, $k) { { "type": $k, "count": $lookup($v, "count") } })

[{ "type": "Actor", "count": 2 }, { "type": "Organization", "count": 2 }]

Aggregating over the whole set (no groupBy) returns a single row:

# Average actor score, no grouping.
from: entities
where: { field: type, eq: Actor }
aggregate: { op: avg, field: score }

{ "score": $average(((analyze.entities)[type = "Actor"]).score) }

[{ "score": 0.765 }]

Aggregate operations: count, { op: sum, field }, { op: avg, field }, { op: min, field }, { op: max, field }. The result column is named after the field (or count for the count case).

Group by multiple fields

groupBy accepts a list — one column per key:

groupBy: [type, status]
aggregate: count

→ rows like [{ type: 'Actor', status: 'active', count: 12 }, …]. One caveat: JSONata stringifies group keys, so multi-key group values come back as strings (a numeric year would appear as "2024"). For a single groupBy, the value keeps its type.

Multiple aggregates, and having

Pass aggregate a map of alias → spec to get one column per alias, and use having to filter the grouped rows (the aggregate-mode counterpart to where):

from: entities
groupBy: type
aggregate:
  n: count
  total: { op: sum, field: score }
having: { field: n, gt: 1 } # keep only groups with more than one row

($each((analyze.entities){type: { "n": $count($), "total": $sum($.score) }}, function($v, $k) { { "type": $k, "n": $lookup($v, "n"), "total": $lookup($v, "total") } }))[n > 1]

[
  { "type": "Actor", "n": 2, "total": 1.53 },
  { "type": "Organization", "n": 2, "total": 1.17 }
]

having references the aggregate columns by their names/aliases, and takes the same operators as where. (Both groups survive here since each has two rows; tighten to gt: 2 and the result is empty.)