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,
    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 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
        )
    )
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]
FOR
    customer_id := BIND_VALUES(ARRAY(101, 102, 103))
    -- unrecognized comments
    -- ORDER_ID := BIND_VALUES(ARRAY[201,202,203]),
;
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/06/20 10:08:10