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. Each collapsing aggregate in one RETURN must use the same key list (mixed lists are not implemented yet). Broadcast a coarser total with OVER (PARTITION BY …).

Write GROUP BY (store) — parentheses are the canonical form. A bare GROUP BY store is accepted in permissive mode; FORMAT (FIX) and strict require the parens. Two or more keys are GROUP BY (region, store).

A RETURN that writes GROUP BY must project those keys. RETURN SUM(x) GROUP BY (store) without store in the return list is UE/STATIC-ANALYSIS-GROUP-BY-KEYS-NOT-RETURNED — that is the silent ANY_VALUE hole. You can still define S := SUM(x) GROUP BY (store) in DEFINE and select only S.

SUM(x) GROUP BY (store) keeps definition grain store. A selected sibling must be determined by those keys; an independent finer column such as region is rejected (UE/STATIC-ANALYSIS-RETURN-FINER-THAN-GROUP-BY). Write a finer grain with GROUP BY (region, store), or broadcast a coarser total with OVER (PARTITION BY store). Composability and purity explains why that grain is part of the function.

Single-key aggregation

The simplest case groups by one key. This example also shows FILTER (WHERE ...) inside an aggregate — that is the only place for pre-aggregation row filters. Query-level WHERE does not shrink what goes into SUM / COUNT; it filters presented rows after resolution.

DEFINE
    item := INPUT(VARCHAR),
    category := INPUT(VARCHAR),
    price := INPUT(DECIMAL(10,2))
RETURN
    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 DESC
;

The COALESCE(..., 0.00) 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. Use a DECIMAL zero here, not 0e0 (DOUBLE), so the types match.

When several measures share the same row restriction, name a boolean once and repeat it in each FILTER (WHERE …). Putting that predicate in query WHERE instead is rejected when it is finer than the RETURN grain (UE/STATIC-ANALYSIS-WHERE-FINER-THAN-RETURN).

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:

DEFINE
    item := INPUT(VARCHAR),
    category := INPUT(VARCHAR),
    price := INPUT(DECIMAL(10,2))
RETURN
    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
;

Query WHERE, ORDER BY, and LIMIT operate on the presented grain (group keys and measures at that grain — a feature need not appear in RETURN to be filterable). Pre-aggregation row filters stay on FILTER (WHERE ...) inside each aggregate.

When an aggregate consumes a feature that is already summarized, FeatureQL also checks whether the metric can be merged over the dimensions being removed. See Metric additivity for additive metrics, semi-additive axes, ratios, and distinct counts.

Count rows, identities, or one contribution per key

COUNT has three legal forms. Write COUNT() for a row count. COUNT(*) is a permissive spelling of that same call; strict mode rejects it (UE/AGGREGATE-COUNT-NULLARY-ONLY). COUNT(expr) is rejected: a count of non-NULL values is COUNT_IF(expr IS NOT NULL).

The last two always return the same number: rows whose key is NULL count in neither. Prefer COUNT(DISTINCT key) to count identities. PER earns its keep in SUM() and AVG(), where a value repeated after a fanout must count once per key.

To count rows that match a predicate, or non-NULL values, write COUNT() FILTER (WHERE …) or COUNT_IF(cond). Aggregation multiplicity covers PER; Units and measures covers the @E unit.

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.

UNNEST() expands one array into rows. You can also select scalars that do not depend on the unnest — they repeat on every output row. Two independent UNNEST() calls in the same RETURN form a cross product.

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:

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

Array of rows

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

DEFINE
    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)
RETURN
    customer_id := row_unnested[customer_id],
    category := row_unnested[category],
    sum_price := row_unnested[sum_price]
ORDER BY
    customer_id,
    category
;

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:

DEFINE
    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]
RETURN
    category,
    sum_sum_price := SUM(sum_price) GROUP BY (category)
ORDER BY category
;

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

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

Multi-level unnesting

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

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

Mixing unnested and non-unnested features

Scalar features that do not depend on the unnested row are repeated for each unnested value:

DEFINE
    other_var := 1,
    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)
RETURN
    customer_id := row_unnested[customer_id],
    other_var
ORDER BY customer_id
;

When to use which