EXTEND(...)

All functions > ARRAY OF ROWS > EXTEND(...)

Adds computed feature fields to a base row or array of rows with field bindings

Syntax

EXTEND(
  base
  WITH feature AS field [, feature AS field ...]
  [ VIA field [, field ...] [ BIND TO feature [, feature ...] ] ]
)

Notes

  • Allows adding computed features as new fields to existing rows
  • BINDING clause maps base fields to feature inputs
  • Features are computed using the bound field values
  • Works with both single rows and arrays of rows
  • New field names must not conflict with existing fields

Related Functions

Examples

Single base row

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 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}

Array of rows

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 := BIND_VALUES(ARRAY[1, 2, 3]),
    ORDER_IDS_DETAILS,
    ARRAY_SUM(ORDER_IDS_DETAILS[ORDER_PRICE]) as REVENUE_PER_CUSTOMER,
;
Result
CUSTOMER_ID BIGINTORDER_IDS_DETAILS VARCHARREVENUE_PER_CUSTOMER VARCHAR
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

Chained EXTEND

FeatureQL
WITH
    CUSTOMERS := ENTITY(),
    CUSTOMER_ID := INPUT(BIGINT#CUSTOMERS),
    DIM_CUSTOMERS := INLINE_COLUMNS(
        customer_id BIGINT#CUSTOMERS BIND TO CUSTOMER_ID,
        name VARCHAR,
        order_list ARRAY(BIGINT#ORDERS),
        ds DATE
        FROM CSV(
            customer_id,name,order_list,ds
            101,Alice,"[200,205]",2025-08-20
            102,Bob,"[201,206]",2025-08-20
            103,Charly,"[202]",2025-08-20
            104,Daniel,"[203]",2025-08-20
            105,Elody,"[]",2025-08-20
            106,Francis,"[204,207]",2025-08-20
        )
    ),
    ORDERS := ENTITY(),
    ORDER_ID := INPUT(BIGINT#ORDERS),
    FCT_ORDERS := INLINE_COLUMNS(
        order_id BIGINT#ORDERS BIND TO ORDER_ID,
        order_customer_id BIGINT#CUSTOMERS,
        order_store_id BIGINT#STORES,
        price DECIMAL(10,2),
        ds DATE
        FROM CSV(
            order_id,order_customer_id,order_store_id,price,ds
            200,101,300,11.00,2025-08-20
            201,102,301,11.00,2025-08-20
            202,103,302,11.00,2025-08-20
            203,101,302,11.00,2025-08-20
            204,106,301,11.00,2025-08-20
            205,101,301,11.00,2025-08-20
            206,102,300,11.00,2025-08-20
            207,106,300,11.00,2025-08-20
        )
    ),
    STORES := ENTITY(),
    STORE_ID := INPUT(BIGINT#STORES),
    DIM_STORES := INLINE_COLUMNS(
        store_id1 BIGINT#STORES BIND TO STORE_ID,
        category VARCHAR,
        ds DATE
        FROM CSV(
            store_id1,category,ds
            300,CAT1,2025-08-20
            301,CAT2,2025-08-20
            302,CAT3,2025-08-20
        )
    ),
    CUSTOMER_ID := BIND_VALUES(ARRAY[101,102,103]),
    -- ORDER_ID := BIND_VALUES(ARRAY[201,202,203]),
SELECT
    ORDER_LIST := DIM_CUSTOMERS[order_list],
    STORE_LIST := EXTEND(ZIP(ORDER_LIST as ORDER_ID) WITH FCT_ORDERS[order_store_id] as order_store_id VIA ORDER_ID BIND TO ORDER_ID),
    CATEGORY_LIST := EXTEND(STORE_LIST WITH DIM_STORES[category] as category VIA order_store_id BIND TO STORE_ID)[category],
Result
ORDER_LIST ARRAYSTORE_LIST VARCHARCATEGORY_LIST VARCHAR
[200, 205][{order_id: 200, order_store_id: 300}, {order_id: 205, order_store_id: 301}][CAT1, CAT2]
[201, 206][{order_id: 201, order_store_id: 301}, {order_id: 206, order_store_id: 300}][CAT2, CAT1]
[202][{order_id: 202, order_store_id: 302}][CAT3]

Suggest changes to this page
Last update at: 2026/05/26 17:22:09