Map, filter & aggregate with TRANSFORM()

TRANSFORM() runs a mini FeatureQL query inside each entity's array of rows — filtering, mapping, aggregating, or sorting the rows without flattening the nested structure. Think of it as RETURN ... WHERE ... GROUP BY ... ORDER BY ..., scoped to one entity's array at a time.

Syntax

TRANSFORM(<expression>
  USING (<inner_query>)
  [KEY <feature>]
  [METHOD <method>]
  [IMPORT <feature>, ...]
)

-- Chained syntax
<expression>.TRANSFORM(<inner_query>)

TRANSFORM(<expression>
  USING (<inner_query>)
  [KEY <feature>]
  [METHOD <method>]
  [IMPORT <feature>, ...]
)

-- Chained syntax
<expression>.TRANSFORM(<inner_query>)

The key parts:

• expression is the array of rows to operate on.
• USING wraps the inner query. Both the operator form and the chained form use parentheses around that query — there is no backtick variant.
• KEY (optional) specifies the aggregation key when FeatureQL cannot auto detect it from the dependency chain. This is needed when the array comes from BIND_VALUES() rather than EXTERNAL_COLUMNS() or INLINE_COLUMNS().
• METHOD (optional) selects the implementation: UNNEST (default; the form this page teaches), LAMBDA, CORRELATED, or ARRAY. Omit it unless you need a non-default method.
• IMPORT (optional) brings external feature definitions into the inner query scope.

The KEY, METHOD, and IMPORT clauses can appear in any order and are all optional.

Inside the inner query, you can use the full range of FeatureQL clauses:

Aggregation: group and summarize

The most common use of TRANSFORM() is to aggregate an array of rows by a grouping key. Here, each customer has a list of products, and we compute the total price per category.

DEFINE
    customer_id := INPUT(BIGINT),
    customer_history := INLINE_COLUMNS(
        customer_id BIGINT BIND TO customer_id,
        products ARRAY(ROW(item VARCHAR, category VARCHAR, price DOUBLE))
        FROM JSON([
            {"customer_id":1, "products":[
                {"item":"item01","category":"A","price":10},
                {"item":"item02","category":"B","price":20}
            ]},
            {"customer_id":2, "products":[
                {"item":"item01","category":"A","price":10},
                {"item":"item02","category":"B","price":20},
                {"item":"item03","category":"B","price":30},
                {"item":"item11","category":"A","price":110},
                {"item":"item12","category":"A","price":120}
            ]}
        ])
    ),
    products := customer_history[products]
RETURN
    customer_id,
    products_transformed := TRANSFORM(
        products
        USING (RETURN
            category,
            sum_price := SUM(price) GROUP BY (category)
        ORDER BY category)
    )
FOR
    customer_id := BIND_VALUES(ARRAY(1, 2))
;

Notice that the inner query uses SUM(price) GROUP BY (category) — the same aggregation syntax as top-level FeatureQL, but scoped to each customer's product array independently. The canonical form uses parentheses even for one key. A bare GROUP BY category is accepted in permissive mode. Multi-key grouping is SUM(price) GROUP BY (category, region). The inner RETURN that writes GROUP BY must project those keys.

Self contained constraint

The inner query must be self contained: it can only reference fields that exist inside the array of rows. You cannot:

• Reference features defined outside the array (use CARRY, EXTEND before TRANSFORM, or IMPORT for pure formulas — see TRANSFORM scope and help('transform_forbidden'))
• Use RELATED(), EXTEND(), RECURSE(), EXTERNAL_COLUMNS(), or legacy EXTERNAL_SQL_* / EXTERNAL_VIEW_* inside the inner query
• Nest another TRANSFORM() inside (chain successive .TRANSFORM() on the array instead)

Full remediation table for LLM help: help('transform_forbidden').

When you need outer values to behave like columns on each row (filters, projections, comparisons inside the inner RETURN), add them to the array before transforming: use CARRY to broadcast scalars onto every row, EXTEND() for join-shaped enrichment, or ARRAY_MERGE when aligning arrays — then TRANSFORM() the enriched array.

IMPORT is different: on TRANSFORM(expr USING (inner) IMPORT f1, …) it brings outer feature definitions into the inner query’s scope so inner can reference those names (for example formulas used inside SUM(...)). It does not, by itself, add fields to the row type. See Overview — Carry .

-- Operator syntax
TRANSFORM(products USING (RETURN category, SUM(price) GROUP BY (category) AS total))

-- Chained syntax
products.TRANSFORM(RETURN category, SUM(price) GROUP BY (category) AS total)