Join on multiple keys with EXTEND()

EXTEND() enriches an array of rows (or a single row) by joining in data from other features. It is the general-purpose join mechanism in FeatureQL — think of it as adding columns to each row based on a foreign key lookup.

While RELATED() handles simple single-key joins concisely, EXTEND() is needed when you want to:

• Add multiple fields at once
• Work with arrays of rows (not just scalar features)
• Use explicit binding to control which keys map to which inputs

Syntax

EXTEND(
  <base_expression>
  WITH <feature> [AS <alias>], ...
  [VIA <field>, ... [BIND TO <input>, ...]]
)

EXTEND(
  <base_expression>
  WITH <feature> [AS <alias>], ...
  [VIA <field>, ... [BIND TO <input>, ...]]
)

The key parts:

• base_expression is the row or array of rows you want to enrich. It contains the foreign keys used for joining.
• WITH lists the features to add as new fields. Each can be aliased with AS.
• VIA specifies which field(s) in the base expression to use as join keys.
• BIND TO (optional) maps those fields to the target entity's input. When entity annotations make the mapping unambiguous, FeatureQL can infer it automatically.

Foreign keys → primary key (lookup)

The most common pattern: each row in an array has a foreign key (like order_id), and you want to add data from the referenced entity (like the order's price). EXTEND looks up each key and appends the result as a new field.

SELECT
    CUSTOMER_ID,
    CUSTOMER_ORDERS := TABLES.AGG_CUSTOMERS_OBT[orders],
    CUSTOMER_ORDERS_DETAILS := EXTEND(
        ZIP(CUSTOMER_ORDERS AS order_id)
        WITH TABLES.AGG_ORDERS_OBT[price] AS ORDER_PRICE
    ),
FROM FM.ECOMM
FOR
    CUSTOMER_ID := BIND_VALUES(SEQUENCE(100,102))
;

Foreign keys → foreign keys (aggregation + lookup)

When the feature you're adding is itself an aggregation across another entity, EXTEND handles the full chain. Here we first compute each order's total price by aggregating items, then extend the customer's order list with those prices.

WITH
    ORDER_PRICE := RELATED(
        SUM(TABLES.FCT_ITEMS[price] * TABLES.FCT_ITEMS[quantity]::DECIMAL) GROUP BY TABLES.FCT_ITEMS[item_order_id]
        VIA ORDER_ID
    )::DECIMAL(10,2)
SELECT
    CUSTOMER_ID,
  	CUSTOMER_ORDERS := TABLES.AGG_CUSTOMERS_OBT[orders],
    CUSTOMER_ORDERS_DETAILS := EXTEND(
        ZIP(CUSTOMER_ORDERS AS order_id)
        WITH ORDER_PRICE as ORDER_PRICE
        VIA order_id BIND TO ORDER_ID
    )
FROM FM.ECOMM
FOR CROSS
    CUSTOMER_ID := BIND_VALUES(SEQUENCE(100,102)),
    ITEM_ID:= BIND_VALUES(SEQUENCE(300,309)),
;

Relationship to RELATED()

For simple single-key joins, RELATED() is more concise. See RELATED() vs EXTEND() for a side-by-side comparison of when to use each.