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 SELECT ... FROM array WHERE ... GROUP BY ... ORDER BY ..., but scoped to a single entity at a time.

Syntax

TRANSFORM(<expression> [BY <feature>]
  USING (<inner_query>)
  [IMPORTING <feature>, ...]
)

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

TRANSFORM(<expression> [BY <feature>]
  USING (<inner_query>)
  [IMPORTING <feature>, ...]
)

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

The key parts:

• expression is the array of rows to operate on.
• BY (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().
• USING / WITH wraps the inner query. Use parentheses for the operator syntax or backticks for the chained syntax.
• IMPORTING (optional) brings external feature definitions into the inner query scope.

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.

WITH
    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]
SELECT
    customer_id,
    TRANSFORM(products USING (
        SELECT
            category,
            sum(price) group by category as sum_price
        order by category
    )) as products_transformed,
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.

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 RELATED() or EXTEND() inside the inner query
• Nest another TRANSFORM() inside

When you need external data inside a transform, perform the enrichment before transforming — use EXTEND() to add fields to the array first, then TRANSFORM() the enriched array. See Common patterns for examples of this workflow.

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

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