Enrich rows with EXTEND()

EXTEND() keeps every field in a row or array of rows and adds fields computed from another entity grain. Use VIA for key-based enrichment and ON for range, as-of, or other predicate-based enrichment.

Syntax

-- Key relationship
EXTEND(
  <base>
  WITH <feature> [AS <field>], ...
  [VIA <base_field>, ... [BIND TO <target>, ...]]
)

-- General predicate
EXTEND(
  <base>
  WITH <aggregate_expression> [AS <field>], ...
  ON <predicate_using_BASE_fields>
)

The base can be one ROW or an ARRAY(ROW). WITH names the values appended to each base row; use AS to control their output field names.

Follow keys with VIA

VIA names fields already present in the base. BIND TO maps them to target inputs when entity types cannot infer the relationship. This self-contained example enriches one order row with two store fields.

FeatureQL
WITH
    -- ** Setup the relational model **
    orders := ENTITY(),
    order_id := INPUT(BIGINT#orders),
    stores := ENTITY(),
    store_id := INPUT(BIGINT#stores),
    -- ** Declare external data sources **
    tables.orders := INLINE_COLUMNS(
        order_id BIGINT#orders BIND TO order_id,
        order_store_id BIGINT#stores
        FROM CSV(
            order_id,order_store_id
            10,20
            11,21
            12,22
            13,21
            14,22
            15,21
        )
    ),
    tables.stores := INLINE_COLUMNS(
        store_id BIGINT#stores BIND TO store_id,
        store_category VARCHAR,
        store_city VARCHAR
        FROM CSV(
            store_id,store_category,store_city
            20,PIZZA,BCN
            21,SUSHI,MAD
            22,BURGER,VAL
        )
    ),
    -- ** Define new features **
    order_store_id := tables.orders[order_store_id],
    order_store_id_details := EXTEND(
        ROW(order_store_id AS order_store_id)
        WITH tables.stores[store_category] AS store_category,
        tables.stores[store_city] AS store_city
        VIA order_store_id BIND TO store_id
    )
SELECT
    order_id,
    order_store_id,
    order_store_id_details
FOR
    order_id := BIND_VALUES(ARRAY(10, 12, 15))
;
Result
ORDER_ID BIGINTORDER_STORE_ID BIGINTORDER_STORE_ID_DETAILS ROW
1020{order_store_id: 20, store_category: PIZZA, store_city: BCN}
1222{order_store_id: 22, store_category: BURGER, store_city: VAL}
1521{order_store_id: 21, store_category: SUSHI, store_city: MAD}

The same form applies to arrays. Start from an existing array of rows, or use ZIP() to turn a scalar key array into named rows before extending it.

FeatureQL
WITH
    -- ** Setup the relational model **
    customers := ENTITY(),
    orders := ENTITY(),
    customer_id := INPUT(BIGINT#customers), -- We just need our primary key CUSTOMER_ID as inputs
    order_id := INPUT(BIGINT#orders),
    -- ** Simulating a customer table. Notice the list of order_ids. **
    customer_source := INLINE_COLUMNS(
        customer_id BIGINT#customers BIND TO customer_id,
        order_ids ARRAY(BIGINT#orders)
        FROM CSV(
            customer_id,order_ids
            1,"[10, 13]"
            2,"[11, 14, 15]"
            3,"[12]"
        )
    ),
    order_ids := customer_source[order_ids],
    -- ** Simulating an order table. Notice there is no customer_id foreign key. **
    order_source := INLINE_COLUMNS(
        order_id BIGINT#orders BIND TO order_id,
        price DOUBLE
        FROM CSV(
            order_id,price
            10,12.51
            11,13.20
            12,15.06
            13,25.70
            14,30.26
            15,10.00
        )
    ),
    -- ** We need to add the price to each entry of the ORDER_IDS feature **
    order_ids_details := EXTEND(
        ZIP(order_ids AS order_id)
        WITH order_source[price] AS order_price
        VIA order_id BIND TO order_id
    )
SELECT
    customer_id,
    order_ids_details,
    revenue_per_customer := ARRAY_SUM(order_ids_details[order_price])
FOR
    customer_id := BIND_VALUES(ARRAY(1, 2, 3))
;
Result
CUSTOMER_ID BIGINTORDER_IDS_DETAILS VARBINARYREVENUE_PER_CUSTOMER DECIMAL
1[{order_id: 10, order_price: 12.51}, {order_id: 13, order_price: 25.70}]38.21
2[{order_id: 11, order_price: 13.20}, {order_id: 14, order_price: 30.26}, {order_id: 15, order_price: 10.00}]53.46
3[{order_id: 12, order_price: 15.06}]15.06

EXTEND() can add a feature that is itself an aggregation at another grain, so key-based enrichment composes naturally across several entity hops.

Match ranges and inequalities with ON

Use ON when no single equi key describes the relationship. Inside the predicate, refer to the current base row as BASE[field] and refer to the nested source through its normal features. Bare base field names are rejected so the two sides remain unambiguous.

FeatureQL
WITH
    product_rows := ZIP(
        ARRAY(1::BIGINT, 2::BIGINT, 3::BIGINT) AS product_id,
        ARRAY(
            52.20::DECIMAL(10,2),
            302.00::DECIMAL(10,2),
            1200.05::DECIMAL(10,2)
        ) AS price
    ),
    bands := ENTITY(),
    band_id := INPUT(BIGINT#bands),
    band_data := INLINE_COLUMNS(
        band_id BIGINT#bands BIND TO band_id,
        name VARCHAR,
        price_min DECIMAL(10,2),
        price_max DECIMAL(10,2)
        FROM CSV(
            band_id,name,price_min,price_max
            100,low,0.,100.
            101,mid,100.,1000.
            102,high,1000.,100000.
        )
    ),
    enriched := EXTEND(
        product_rows
        WITH MAX_BY(BAND_DATA[NAME], BAND_DATA[PRICE_MAX]) AS price_band
        ON BAND_DATA[PRICE_MIN] <= BASE[PRICE]
           AND BASE[PRICE] < BAND_DATA[PRICE_MAX]
    )
SELECT
    enriched
FOR
    NESTED band_id := BIND_VALUES(ARRAY(100, 101, 102))
;
Result
ENRICHED VARBINARY
[{product_id: 1, price: 52.20, price_band: low}, {product_id: 2, price: 302.00, price_band: mid}, {product_id: 3, price: 1200.05, price_band: high}]

The complete match—including any equality conjuncts—belongs in ON. Do not combine ON with VIA or BIND TO.

Collapse matches in WITH

An ON predicate may match several inner rows. Every added WITH value must therefore be an aggregate such as MAX_BY(), COUNT(), or ARRAY_AGG(). EXTEND() preserves each base row and returns the aggregate's normal empty-match result: NULL for MAX_BY(), 0 for COUNT(*).

As-of enrichment follows the same pattern: match events at or before BASE[ref_ts], then use MAX_BY(value, ts) to select the latest event.

FeatureQL
WITH
    accounts := ENTITY(),
    account_rows := ZIP(
        ARRAY(1::BIGINT, 2::BIGINT) AS account_id,
        ARRAY(TIMESTAMP '2024-06-15 12:00:00', TIMESTAMP '2024-06-20 12:00:00') AS ref_ts
    ),
    events := ENTITY(),
    event_id := INPUT(BIGINT#events),
    event_data := INLINE_COLUMNS(
        event_id BIGINT#events BIND TO event_id,
        account_id BIGINT#accounts,
        event_ts TIMESTAMP,
        status VARCHAR
        FROM CSV(
            event_id,account_id,event_ts,status
            1,1,2024-06-01 10:00:00,new
            2,1,2024-06-10 10:00:00,active
            3,1,2024-06-20 10:00:00,paused
            4,2,2024-06-05 10:00:00,new
            5,2,2024-06-25 10:00:00,active
        )
    ),
    enriched := EXTEND(
        account_rows
        WITH MAX_BY(EVENT_DATA[STATUS], EVENT_DATA[EVENT_TS]) AS last_status
        ON EVENT_DATA[ACCOUNT_ID] = BASE[ACCOUNT_ID]
           AND EVENT_DATA[EVENT_TS] <= BASE[REF_TS]
    )
SELECT
    enriched
FOR
    NESTED event_id := BIND_VALUES(ARRAY(1, 2, 3, 4, 5))
;
Result
ENRICHED VARBINARY
[{account_id: 1, ref_ts: 2024-06-15T12:00:00, last_status: active}, {account_id: 2, ref_ts: 2024-06-20T12:00:00, last_status: new}]

Bind inputs needed only by the source being matched as NESTED; they define the inner relation without multiplying the outer query rows.

When to use another operation

  • Use RELATED() when only one scalar or row is needed at the outer entity grain.
  • Use EXTEND() when base rows should survive and gain fields from another grain.
  • Use ARRAY_MERGE() when both relations are already arrays and all matches should fan out for later processing.
Last update at: 2026/08/04 11:25:38