Cross-entity joins with RELATED()

RELATED() produces one value per outer entity by looking up or aggregating data at another entity grain. Use VIA for key relationships and ON when the match itself is a general boolean predicate.

Syntax

-- Key relationship
RELATED(<expression> VIA <key> [BIND TO <target>])
<key>.RELATED(<expression> [GROUP BY <foreign_key>] [BIND TO <target>])

-- General predicate
RELATED(<aggregate_expression> ON <predicate>)

The two forms solve different relationship problems:

VIA follows a key. Use it for foreign-key lookups and grouped aggregations. BIND TO names the target input when entity annotations cannot infer it.
ON evaluates a predicate. Use it for ranges, as-of conditions, inequalities, and predicates that combine equality with another condition. The complete match belongs in ON; do not add VIA or BIND TO.

When features already share the same input, they align automatically and need neither form.

Follow a foreign key with VIA

For a lookup, VIA points to the foreign key on the outer entity. This self-contained example maps each order's store ID to the store category.

FeatureQL

WITH
    -- ** Features defining the semantic entities [likely already persisted in the registry] **
    orders := ENTITY(),
    order_id := INPUT(BIGINT#orders),
    stores := ENTITY(),
    store_id := INPUT(BIGINT#stores),
    -- ** Features depending on ORDER_ID [likely already persisted in the registry] **
    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
        )
    ),
    -- ** Features depending on STORE_ID [likely already persisted in the registry] **
    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
        )
    ),
    -- ** New feature depending on ORDER_ID **
    order_category := RELATED(
        tables.stores[store_category] VIA tables.orders[order_store_id]
    )
SELECT
    order_id,
    order_category
FOR
    order_id := BIND_VALUES(SEQUENCE(10, 15))
ORDER BY order_id
;

Result

ORDER_ID BIGINT	ORDER_CATEGORY VARCHAR
10	PIZZA
11	SUSHI
12	BURGER
13	SUSHI
14	BURGER
15	SUSHI

Aggregate child rows with VIA

For a one-to-many relationship, aggregate at the child grain and group by the foreign key that points back to the outer entity. Bind the child input as NESTED so its rows are available to the inner aggregation.

FeatureQL

WITH
    -- ** Define features at ORDER level **
    orders := ENTITY(),
    order_id := INPUT(BIGINT#orders),
    order_source := 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
        )
    ),
    -- ** Define features at STORE level **
    stores := ENTITY(),
    store_id := INPUT(BIGINT#stores),
    store_source := 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
        )
    ),
    num_orders := RELATED(
        SUM(1) GROUP BY order_source[order_store_id] VIA store_id
    )
SELECT
    store_id,
    num_orders
FOR
    store_id := BIND_VALUES(ARRAY(20, 21, 22)),
    NESTED order_id := BIND_VALUES(SEQUENCE(10, 15)) -- Orders-side INPUT for RELATED(... GROUP BY ...); bind as NESTED (subquery scope)
ORDER BY store_id
;

Result

STORE_ID BIGINT	NUM_ORDERS BIGINT
20	1
21	3
22	2

These two operations compose: aggregate at one entity grain, then follow a foreign key to bring that scalar result to another grain.

Match ranges and inequalities with ON

ON is the join predicate, not a post-join filter. The predicate must relate the outer query grain to one nested inner grain. Because several inner rows may match, the expression before ON must aggregate them; MAX_BY() is a natural choice for labeled ranges.

FeatureQL

WITH
    products := ENTITY(),
    product_id := INPUT(BIGINT#products),
    product_data := INLINE_COLUMNS(
        product_id BIGINT#products BIND TO product_id,
        price DECIMAL(10,2)
        FROM CSV(
            product_id,price
            1,52.20
            2,302.00
            3,1200.05
        )
    ),
    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.
        )
    )
SELECT
    product_id,
    price := product_data[price],
    price_band := RELATED(
        MAX_BY(band_data[name], band_data[price_max]) ON band_data[price_min]
        <= product_data[price]
        AND product_data[price] < band_data[price_max]
    )
FOR
    product_id := BIND_VALUES(ARRAY(1, 2, 3)),
    NESTED band_id := BIND_VALUES(ARRAY(100, 101, 102))
ORDER BY product_id
;

Result

PRODUCT_ID BIGINT	PRICE DECIMAL	PRICE_BAND VARCHAR
1	52.20	low
2	302.00	mid
3	1200.05	high

Equality can be one conjunct of the predicate—for example, category equality plus a price range—but it stays inside ON. Use FILTER (WHERE ...) separately when an aggregate also needs row filtering after matching.

Build as-of features with ON

As-of joins are the same pattern: match all events at or before the outer timestamp, then use MAX_BY(value, timestamp) to select the latest one.

FeatureQL

WITH
    accounts := ENTITY(),
    account_id := INPUT(BIGINT#accounts),
    account_data := INLINE_COLUMNS(
        account_id BIGINT#accounts BIND TO account_id,
        as_of_date DATE
        FROM CSV(
            account_id,as_of_date
            1,2024-01-10
            2,2024-01-20
        )
    ),
    events := ENTITY(),
    event_id := INPUT(BIGINT#events),
    event_data := INLINE_COLUMNS(
        event_id BIGINT#events BIND TO event_id,
        event_date DATE,
        status VARCHAR
        FROM CSV(
            event_id,event_date,status
            1,2024-01-01,new
            2,2024-01-05,active
            3,2024-01-15,paused
        )
    )
SELECT
    account_id,
    as_of_date := account_data[as_of_date],
    last_status := RELATED(
        MAX_BY(event_data[status], event_data[event_date]) ON event_data[event_date]
        <= account_data[as_of_date]
    )
FOR
    account_id := BIND_VALUES(ARRAY(1, 2)),
    NESTED event_id := BIND_VALUES(ARRAY(1, 2, 3))
ORDER BY account_id
;

Result

ACCOUNT_ID BIGINT	AS_OF_DATE TIMESTAMP	LAST_STATUS VARCHAR
1	2024-01-10	active
2	2024-01-20	paused

RELATED(... ON ...) preserves the outer entity rows. Aggregates return their normal empty-match result, such as NULL for MAX_BY() or 0 for COUNT(*).

Choosing RELATED, EXTEND, or ARRAY_MERGE

Use RELATED() when the result should be one scalar or row per outer entity.
Use EXTEND() when each base row should retain its fields and gain aggregate fields from another grain.
Use ARRAY_MERGE() when both sides are already arrays and matching rows should fan out before a later TRANSFORM().

Last update at: 2026/08/04 11:25:38

On this page

Syntax Follow a foreign key with VIA Aggregate child rows with VIA Match ranges and inequalities with ON Build as-of features with ON Choosing RELATED, EXTEND, or ARRAY_MERGE