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(DOUBLE),
    (item, category, price) := BIND_VALUES(
        ARRAY[
            ROW('item1', 'A', 10e0),
            ROW('item2', 'B', 20e0),
            ROW('item3', 'A', 20e0),
            ROW('item4', 'C', 40e0)
        ]
    )
SELECT
    category,
    COALESCE((SUM(price) FILTER (WHERE price <.2 35e0) GROUP BY category), 0e0) AS sum_price  -- C 40 is excluded here therefore sum for C is NULL
WHERE sum_price <.2 25e0  -- A 30 is excluded
ORDER BY sum_price 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(DOUBLE),
    (item, category, price) := BIND_VALUES(
        ARRAY[
            ROW('item01', 'A', 10e0),
            ROW('item02', 'C', 20e0),
            ROW('item03', 'B', 20e0),
            ROW('item11', 'C', 110e0),
            ROW('item12', 'A', 120e0)
        ]
    )
SELECT
    category,
    item_cat := item.substr(1,5),
    SUM(price) FILTER (WHERE price <.2 35e0) GROUP BY category, item_cat AS sum_price
WHERE sum_price >=.2 10e0
ORDER BY sum_price DESC, category
LIMIT 2

Result

CATEGORY VARCHAR	ITEM_CAT VARCHAR	SUM_PRICE VARCHAR
B	item0	20.0
C	item0	20.0

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', 10e0, 1),
        ROW('B', 20e0, 1),
        ROW('A', 240e0, 2),
        ROW('B', 50e0, 2),
    ]::ARRAY(ROW(category VARCHAR, sum_price DOUBLE, 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.0
1	B	20.0
2	A	240.0
2	B	50.0

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', 10e0, 1),
        ROW('B', 20e0, 1),
        ROW('A', 240e0, 2),
        ROW('B', 50e0, 2),
    ]::ARRAY(ROW(CATEGORY VARCHAR, SUM_PRICE DOUBLE, CUSTOMER_ID BIGINT)),
    ROW_UNNESTED := UNNEST(ARRAY_OF_ROWS),
    CATEGORY := ROW_UNNESTED[CATEGORY],
    SUM_PRICE := ROW_UNNESTED[SUM_PRICE],
SELECT
    CATEGORY,
    SUM(SUM_PRICE) GROUP BY CATEGORY AS SUM_SUM_PRICE
ORDER BY CATEGORY
;

Result

CATEGORY VARCHAR	SUM_SUM_PRICE VARCHAR
A	250.0
B	70.0

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_LENGTH(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:

FeatureQL

WITH
    other_var := 1,
    array_of_rows := ARRAY[
        ROW('A', 10e0, 1),
        ROW('B', 20e0, 1),
        ROW('A', 240e0, 2),
        ROW('B', 50e0, 2),
    ]::ARRAY(ROW(category VARCHAR, sum_price DOUBLE, customer_id BIGINT)),
    ROW_UNNESTED := UNNEST(array_of_rows),
SELECT
    customer_id := ROW_UNNESTED[customer_id],
    other_var
ORDER BY customer_id;

Result

ERROR UE/UNNEST-WITH-REPORT Only return unnested features and features derived from them. Don't include any other features in the result set. In this case: ['OTHER_VAR'].

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/03/03 16:47:38

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