Add aggregated fields

Here's an example of how to use ADD_FIELDS with an aggregation.

Modeling notes

Directly using the aggregation function with ADD_FIELDS:

country,
revenue_by_country := ADD_FIELDS(
    SUM(price) GROUP BY order_country as revenue_by_country
    TO ROW(country)
    BINDING FIELD country WITH order_country
)[revenue_by_country]
null

Is equivalent to transforming with filtering and aggregation on an array of rows generated via ARRAY_AGG(foreign_key) GROUP BY primary_key.

country,
-- country_orders := ARRAY_AGG(ROW(order_id, price)) GROUP BY order_country, -- That we cannot do because of the need to bind FK to PK
country_orders := ADD_FIELDS(
    ARRAY_AGG(ROW(order_id, price)) GROUP BY order_country as country_orders
    TO ROW(country)
    BINDING FIELD country WITH order_country
)[country_orders], -- Can be persisted
revenue_by_country := country_orders.TRANSFORM(SELECT SUM(price)).unwrap_one(),
null

The latter is more verbose but also more flexible as it allows any sort of operation on the "country_orders" array of rows that can be persisted.

Suggest changes to this page
Last update at: 2025/10/13 10:23:46