Enrich rows with EXTEND()

EXTEND() keeps every field in a row or array of rows and adds fields computed from another entity grain. It behaves like a left join that preserves the base shape and collapses matches into added fields rather than fanning out. 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
DEFINE
    -- ** 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
    )
RETURN
    order_id,
    order_store_id,
    order_store_id_details
FOR
    order_id := BIND_VALUES(ARRAY(10, 12, 15))
;
Result
ORDER_ID BIGINT#ORDERSORDER_STORE_ID BIGINT#STORESORDER_STORE_ID_DETAILS ROW<order_store_id BIGINT#STORES, store_category VARCHAR, store_city VARCHAR>
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
DEFINE
    -- ** 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, 99]"
        )
    ),
    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
    )
RETURN
    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 BIGINT#CUSTOMERSORDER_IDS_DETAILS ARRAY<ROW<order_id BIGINT#ORDERS, order_price DOUBLE>>REVENUE_PER_CUSTOMER DOUBLE
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}, {order_id: 99, order_price: NULL}]15.06

The final order ID has no matching source row. It remains in the array with a NULL price, making the left-preserving behavior explicit.

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

Chain enrichment across hops

Each EXTEND() can use a field added by the previous one. This example follows order ID → store ID → category while retaining the original order rows.

FeatureQL
DEFINE
    orders := ENTITY(),
    order_id := INPUT(BIGINT#orders),
    order_data := INLINE_COLUMNS(
        order_id BIGINT#orders BIND TO order_id,
        store_id BIGINT#stores
        FROM CSV(
            order_id,store_id
            10,20
            11,21
            12,22
        )
    ),
    stores := ENTITY(),
    store_id := INPUT(BIGINT#stores),
    store_data := INLINE_COLUMNS(
        store_id BIGINT#stores BIND TO store_id,
        category VARCHAR
        FROM CSV(
            store_id,category
            20,PIZZA
            21,SUSHI
            22,BURGER
        )
    ),
    order_rows := ZIP(ARRAY(10, 11, 12) AS order_id),
    orders_with_store := EXTEND(
        order_rows
        WITH order_data[store_id] AS store_id
        VIA order_id BIND TO order_id
    ),
    enriched := EXTEND(
        orders_with_store
        WITH store_data[category] AS category
        VIA store_id BIND TO store_id
    )
RETURN
    enriched
;
Result
ENRICHED ARRAY<ROW<order_id BIGINT, store_id BIGINT#STORES, category VARCHAR>>
[{order_id: 10, store_id: 20, category: PIZZA}, {order_id: 11, store_id: 21, category: SUSHI}, {order_id: 12, store_id: 22, category: BURGER}]

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
DEFINE
    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]
    )
RETURN
    enriched
FOR
    NESTED band_id := BIND_VALUES(ARRAY(100, 101, 102))
;
Result
ENRICHED ARRAY<ROW<product_id BIGINT, price DECIMAL(10,2), price_band VARCHAR>>
[{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
DEFINE
    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]
    )
RETURN
    enriched
FOR
    NESTED event_id := BIND_VALUES(ARRAY(1, 2, 3, 4, 5))
;
Result
ENRICHED ARRAY<ROW<account_id BIGINT, ref_ts TIMESTAMP, last_status VARCHAR>>
[{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/10/13 08:32:21