aggregate
The aggregate strategy groups rows and computes metrics. It extends the join strategy — the same primary and join syntax applies — adding a group_by and a metrics block.
Compiles to: scan → join → aggregate → sort → page
Use this strategy for summaries, leaderboards, dashboards, and any read model that requires counts, sums, or other computed metrics per group.
Example
domain:
projections:
user_activity:
strategy: aggregate
primary:
collection: hello_messages
as: m
join:
- collection: users
as: u
type: left
on:
"==":
- var: m.user_id
- var: u.id
fields:
- u.name as author_name
group_by: [m.user_id, author_name]
metrics:
- id: message_count
op: count
- id: last_posted_at
op: max
field: m.created_at
- id: first_posted_at
op: min
field: m.created_at
sort:
default_by: message_count
default_order: desc
pagination:
mode: page
page_arg: page
page_size_arg: page_size
default_page_size: 20
max_page_size: 100
primary and join
The primary and join blocks work identically to the join strategy. The join is applied before grouping, so joined fields are available in group_by and metrics.
primary:
collection: hello_messages
as: m
join:
- collection: users
as: u
type: left
on:
"==": [{ var: m.user_id }, { var: u.id }]
fields:
- u.name as author_name
group_by
group_by lists the fields used to form groups. Each unique combination of values for these fields becomes one row in the result.
group_by: [m.user_id, author_name]
Fields reference the primary alias (m.*) or joined aliases by name. Every field in group_by appears in the result row alongside the computed metrics.
metrics
metrics declares the computed values for each group.
metrics:
- id: message_count
op: count
- id: last_posted_at
op: max
field: m.created_at
- id: first_posted_at
op: min
field: m.created_at
- id: total_chars
op: sum
field: m.char_count
- id: avg_length
op: avg
field: m.char_count
op | field required | Description |
|---|---|---|
count | No | Count of rows in the group |
sum | Yes | Sum of the field across all rows in the group |
min | Yes | Minimum value of the field in the group |
max | Yes | Maximum value of the field in the group |
avg | Yes | Average value of the field in the group |
Each metric appears in the result row under its id.
sort
Sorting in the aggregate strategy can reference both group_by fields and metric IDs.
sort:
by_arg: sort_by
order_arg: sort_order
allowed: [message_count, last_posted_at, author_name]
default_by: message_count
default_order: desc
pagination
Pagination works identically to other strategies. The total count returned is the number of distinct groups, not the number of raw rows scanned.
When to use aggregate vs timeline
aggregate computes metrics from the current state of collections. If a message is deleted, its contribution to message_count disappears immediately because the underlying row is gone.
timeline accumulates state from signal history. A timeline projection can maintain a message_count that reflects the full history of creates and deletes, even if the underlying records no longer exist. Use aggregate for current-state summaries. Use timeline for event-sourced accumulators.