Cross-entity joins with RELATED()

RELATED() produces one value per outer entity by looking up or aggregating data at another entity grain. It is left-join-like: outer rows survive unmatched, but matches collapse to one value instead of fanning out. 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. The final order points to a missing store and remains in the result with a NULL category.

FeatureQL

DEFINE
    -- ** 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
            16,99
        )
    ),
    -- ** 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]
    )
RETURN
    order_id,
    order_category
FOR
    order_id := BIND_VALUES(SEQUENCE(10, 16))
ORDER BY order_id
;

Result

ORDER_ID BIGINT#ORDERS	ORDER_CATEGORY VARCHAR
10	PIZZA
11	SUSHI
12	BURGER
13	SUSHI
14	BURGER
15	SUSHI
16	NULL

Aggregate and filter 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. Use FILTER (WHERE ...) for conditional measures, and bind the child input as NESTED so its rows are available to the inner aggregation.

FeatureQL

DEFINE
    -- ** 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,
        price DECIMAL(10,2)
        FROM CSV(
            order_id,order_store_id,price
            10,20,12.51
            11,21,13.20
            12,22,15.06
            13,21,25.70
            14,22,30.26
            15,21,10.00
        )
    ),
    -- ** Define features at STORE level **
    stores := ENTITY(),
    store_id := INPUT(BIGINT#stores),
    num_orders := RELATED(
        COUNT() GROUP BY (order_source[order_store_id]) VIA store_id
    ),
    num_high_value_orders := RELATED(
        COUNT()
        FILTER (WHERE order_source[price] > 20.00)
        GROUP BY (order_source[order_store_id]) VIA store_id
    ),
    has_orders := COALESCE(num_orders, 0) > 0
RETURN
    store_id,
    num_orders,
    num_high_value_orders,
    has_orders
FOR
    store_id := BIND_VALUES(ARRAY(20, 21, 22, 23)),
    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#STORES	NUM_ORDERS BIGINT	NUM_HIGH_VALUE_ORDERS BIGINT	HAS_ORDERS BOOLEAN
20	1	0	TRUE
21	3	1	TRUE
22	2	1	TRUE
23	0	0	FALSE

The store with no orders shows the empty-group contract: COUNT() is 0 when no child group exists and when a group exists but its filter matches nothing. Other aggregates, such as SUM() and MAX(), are NULL for that empty match. COUNT() > 0 produces an EXISTS/semi-join flag; negate it for NOT EXISTS/anti-join behavior, then use WHERE when unmatched outer rows should be removed.

These 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

DEFINE
    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.
        )
    )
RETURN
    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#PRODUCTS	PRICE DECIMAL(10,2)	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 the entity key and all events at or before the outer timestamp, then use MAX_BY(value, timestamp) to select the latest one.

FeatureQL

DEFINE
    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,
        account_id BIGINT#accounts,
        event_date DATE,
        status VARCHAR
        FROM CSV(
            event_id,account_id,event_date,status
            1,1,2024-01-01,new
            2,1,2024-01-05,active
            3,1,2024-01-15,paused
            4,2,2024-01-05,new
            5,2,2024-01-25,active
        )
    )
RETURN
    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[account_id]
        = account_id
        AND 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, 4, 5))
ORDER BY account_id
;

Result

ACCOUNT_ID BIGINT#ACCOUNTS	AS_OF_DATE DATE	LAST_STATUS VARCHAR
1	2024-01-10	active
2	2024-01-20	new

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 every matching pair should fan out before a later TRANSFORM().

Last update at: 2026/10/13 08:32:21

On this page

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