Aggregates — AQL

Aggregate select

count

1
select count(User);

1
SELECT COUNT(*) FROM (
2
  SELECT 1 FROM "user" u
3
) _agg;

With a filter:

1
select count(User filter .active = true);

1
SELECT COUNT(*) FROM (
2
  SELECT 1 FROM "user" u
3
  WHERE u.active = true
4
) _agg;

As a scalar subquery

An aggregate can also be wrapped in parentheses and used as a scalar operand anywhere an expression is accepted — inside a filter, or on the right-hand side of an update set assignment. It compiles to the same SELECT COUNT(*) wrapped in parentheses, and its filter may correlate to the outer row.

1
multi select User { id, email }
2
filter (select count(Post filter .author.id = User.id)) > 0;

1
SELECT
2
  u.id AS id,
3
  u.email AS email
4
FROM "user" u
5
WHERE (SELECT COUNT(*) FROM (
6
  SELECT 1 FROM "post" p
7
  WHERE p.author = u.id
8
) _agg) > 0;

The inner filter .author.id = User.id references the outer alias (u.id), so the count is evaluated per user. The same form works as an assignment value — set { has_posts := (select count(Post filter .author.id = User.id)) > 0 }.

Note: an aggregate subquery is only valid as an expression operand. It is not accepted as a computed shape field value ({ n := (select count(...)) }).

Aggregate shape — many aggregates in one scan

A select whose shape fields are aggregates computes several aggregates over the same set in a single pass. Each field is name := <agg>(.column) with an optional per-field filter, and the select’s own filter (after the shape, as usual) is the shared condition applied to every field:

1
select Transaction {
2
  success_debit  := sum(.amount) filter .type = TransactionType.Debit  and .status = TransactionStatus.Successful,
3
  pending_debit  := sum(.amount) filter .type = TransactionType.Debit  and .status = TransactionStatus.Pending,
4
  success_credit := sum(.amount) filter .type = TransactionType.Credit and .status = TransactionStatus.Successful,
5
  pending_credit := sum(.amount) filter .type = TransactionType.Credit and .status = TransactionStatus.Pending,
6
}
7
filter (.sender_id = $api_key_id and .sender_entity = TransactionActorEntity.ApiKey)
8
    or (.reciever_id = $api_key_id and .reciever_entity = TransactionActorEntity.ApiKey);

Each field lowers to a Postgres FILTER (WHERE …) aggregate, so the whole query is one scan — no correlated subqueries:

1
SELECT
2
  SUM(t.amount) FILTER (WHERE t.type = 'Debit'  AND t.status = 'Successful') AS success_debit,
3
  SUM(t.amount) FILTER (WHERE t.type = 'Debit'  AND t.status = 'Pending')    AS pending_debit,
4
  SUM(t.amount) FILTER (WHERE t.type = 'Credit' AND t.status = 'Successful') AS success_credit,
5
  SUM(t.amount) FILTER (WHERE t.type = 'Credit' AND t.status = 'Pending')    AS pending_credit
6
FROM "transaction" t
7
WHERE (t.sender_id = $1 AND t.sender_entity = 'ApiKey')
8
   OR (t.reciever_id = $1 AND t.reciever_entity = 'ApiKey');

The result is a single row (one *Row in generated code); multi, order by, limit, and offset are not allowed.

Rules

Aggregate functions: sum, avg, min, max, count. count() (no argument) is COUNT(*); the others take an argument expression (such as .column, or a math / function expression like min(haversine(.loc.lat, .loc.lon, $target_lat, $target_lon))). The per-field filter is optional.
A shape is an aggregate shape as soon as one field is an aggregate; every field must then be an aggregate — mixing aggregates with plain row fields requires a Group By clause.
Result types. Aggregate fields are nullable (an aggregate over zero rows is NULL). count is int64; min/max keep the column’s type. sum and avg change type in Postgres (e.g. sum of a bigint column is numeric), so add a cast to pin the generated type — sum(.amount)<int64> — otherwise the field is typed as any and code generation warns.