Grouping & Unnesting

FeatureQL features normally produce one value per entity. GROUP BY and UNNEST() change that — they let you return results at a different granularity than the input, either coarser (aggregated) or finer (flattened).

Grouping with GROUP BY

In FeatureQL, GROUP BY lives inside aggregate functions rather than as a separate clause. This keeps each aggregation self-contained — different features in the same query can group by different keys.

Single-key aggregation

The simplest case groups by one key. This example also shows FILTER (WHERE ...) inside an aggregate, which applies a condition during aggregation without affecting other features:

FeatureQL

WITH
    item := INPUT(VARCHAR),
    category := INPUT(VARCHAR),
    price := INPUT(DECIMAL(10,2))
SELECT
    category,
    sum_price := COALESCE(
        (SUM(price) FILTER (WHERE price < 35.00) GROUP BY category), -- C 40 is excluded here therefore sum for C is NULL
        0.00
    )
FOR
    (item, category, price) := BIND_VALUES(
        ARRAY(
            ROW('item1', 'A', 10.01),
            ROW('item2', 'B', 20.00),
            ROW('item3', 'A', 20.00),
            ROW('item4', 'C', 40.00)
        )
    )
WHERE sum_price
        < 25.00 -- A 30 is excluded
ORDER BY sum_price -- C 40 is excluded here therefore sum for C is NULL DESC
;

Result

CATEGORY VARCHAR	SUM_PRICE VARCHAR
B	20.0
C	0.0

The COALESCE(..., 0e0) handles the case where all rows for a group are filtered out — category C has a price of 40 which exceeds the filter threshold, so SUM() returns NULL, which COALESCE() replaces with zero.

Multi-key aggregation

You can group by multiple keys within the same aggregate function. This example groups by both category and a derived feature (item_cat), then filters and sorts the results:

FeatureQL

WITH
    item := INPUT(VARCHAR),
    category := INPUT(VARCHAR),
    price := INPUT(DECIMAL(10,2))
SELECT
    category,
    item_cat := item.SUBSTR(1, 5),
    sum_price := SUM(price)
    FILTER (WHERE price < 35.00)
    GROUP BY category, item_cat
FOR
    (item, category, price) := BIND_VALUES(
        ARRAY(
            ROW('item01', 'A', 10.00),
            ROW('item02', 'C', 20.00),
            ROW('item03', 'B', 20.00),
            ROW('item11', 'C', 110.00),
            ROW('item12', 'A', 120.00)
        )
    )
WHERE sum_price >= 10
ORDER BY
    sum_price DESC,
    category
LIMIT 2
;

Result

CATEGORY VARCHAR	ITEM_CAT VARCHAR	SUM_PRICE VARCHAR
B	item0	20.00
C	item0	20.00

The WHERE, ORDER BY, and LIMIT clauses operate on the aggregated results, not the raw input rows.

Unnesting with UNNEST()

UNNEST() does the opposite of aggregation: it takes an array and expands each element into its own row. This is how you flatten nested structures for reporting or further processing.

Exclusive output

UNNEST() is exclusive — you can only return unnested features and features derived from them. You cannot mix unnested features with other features in the same SELECT.

Scalar arrays

To unnest a plain array (not an array of rows), wrap it with ZIP() first to convert it into an array of rows, then UNWRAP() the single-field result:

FeatureQL

WITH
    nested_values := ARRAY(1, 2, 3)
SELECT
    computed_value := UNNEST(ZIP(nested_values)).UNWRAP() + 1
;

Result

COMPUTED_VALUE BIGINT
2
3
4

Array of rows

Unnesting an array of rows produces one output row per element. Access fields from the unnested row using the [] operator:

FeatureQL

WITH
    array_of_rows := ARRAY(
        ROW('A', 10.00, 1),
        ROW('B', 20.00, 1),
        ROW('A', 240.00, 2),
        ROW('B', 50.00, 2)
    )::ARRAY(ROW(category VARCHAR, sum_price DECIMAL(10,2), customer_id BIGINT)),
    row_unnested := UNNEST(array_of_rows)
SELECT
    customer_id := row_unnested[customer_id],
    category := row_unnested[category],
    sum_price := row_unnested[sum_price]
ORDER BY
    customer_id,
    category
;

Result

CUSTOMER_ID BIGINT	CATEGORY VARCHAR	SUM_PRICE VARCHAR
1	A	10.00
1	B	20.00
2	A	240.00
2	B	50.00

Unnest then aggregate

A common pattern: unnest an array of rows, then aggregate the flattened data with GROUP BY. This lets you re-aggregate nested data at a different granularity:

FeatureQL

WITH
    array_of_rows := ARRAY(
        ROW('A', 10.00, 1),
        ROW('B', 20.00, 1),
        ROW('A', 240.00, 2),
        ROW('B', 50.00, 2)
    )::ARRAY(ROW(category VARCHAR, sum_price DECIMAL(10,2), customer_id BIGINT)),
    row_unnested := UNNEST(array_of_rows),
    category := row_unnested[category],
    sum_price := row_unnested[sum_price]
SELECT
    category,
    sum_sum_price := SUM(sum_price) GROUP BY category
ORDER BY category
;

Result

CATEGORY VARCHAR	SUM_SUM_PRICE VARCHAR
A	250.00
B	70.00

Preserving position with ordinality

If you need to know each element's position in the original array, generate an ordinality column using SEQUENCE() inside ZIP():

FeatureQL

WITH
    nested_values := ARRAY('C', 'B', 'A'),
    unnested_value := UNNEST(
        ZIP(
            nested_values AS value,
            SEQUENCE(1, ARRAY_COUNT(nested_values)) AS ordinality
        )
    )
SELECT
    ordinality := unnested_value[ordinality],
    value := unnested_value[value]
ORDER BY ordinality
;

Result

ORDINALITY BIGINT	VALUE VARCHAR
1	C
2	B
3	A

Multi-level unnesting

Nested arrays (arrays of arrays) require multiple UNNEST() calls. Each level flattens one layer:

FeatureQL

WITH
    nested_values := ARRAY(ARRAY(1, 2, 3), ARRAY(4, 5), ARRAY(6)),
    unnested_value1 := UNNEST(ZIP(nested_values)).UNWRAP()
SELECT
    unnested_value2 := UNNEST(ZIP(unnested_value1)).UNWRAP()
;

Result

UNNESTED_VALUE2 BIGINT
1
2
3
4
5
6

Mixing unnested and non-unnested features

Attempting to include non-unnested features alongside unnested ones produces an error — FeatureQL enforces this to prevent ambiguous row counts:

@fql-playground(unnest_with_report_unallowed)

When to use which

Goal	Approach
Aggregate to a coarser granularity	`GROUP BY` inside aggregate functions
Flatten arrays for reporting	`UNNEST()`
Filter within aggregation	`FILTER (WHERE ...)` inside the aggregate
Aggregate nested data differently	`UNNEST()` first, then `GROUP BY`
Operate on arrays without flattening	Use `TRANSFORM()` — see TRANSFORM

Suggest changes to this page

Last update at: 2026/06/20 10:08:10

On this page

Grouping with GROUP BY Single-key aggregation Multi-key aggregation Unnesting with UNNEST()Scalar arrays Array of rows Unnest then aggregate Preserving position with ordinality Multi-level unnesting Mixing unnested and non-unnested features When to use which