Lookup with indexes

When you need to look up specific rows in an array by a field value, TRANSFORM() with a WHERE clause works but scans the entire array every time. For repeated lookups — especially against the same array — building an index first is significantly more efficient.

Filtering with TRANSFORM (baseline)

The straightforward approach: use TRANSFORM() to filter rows by a field value. This works well for one-off filters but scans the full array on each call.

DEFINE
    base := ARRAY(
        ROW('col1a' AS col1, 'col2a' AS col2),
        ROW('col1b' AS col1, 'col2b' AS col2),
        ROW('col1c' AS col1, 'col2b' AS col2)
    ),
    base_filtered_one := TRANSFORM(
        base
        USING (RETURN *
        WHERE col2 = 'col2b'
        LIMIT 1)
    ),
    base_filtered_many := TRANSFORM(
        base
        USING (RETURN *
        WHERE col2 = 'col2b')
    )
RETURN
    base_filtered_one,
    base_filtered_many
;

Unique index: one row per key

When the field you're searching on has unique values (like an ID), INDEX_UNIQUE() builds a map from field values to rows. Use ELEMENT_AT_KEY() to look up a single row by key — returning NULL if the key doesn't exist.

DEFINE
    base := ARRAY(
        ROW('col1a' AS col1, 'col2a' AS col2),
        ROW('col1b' AS col1, 'col2b' AS col2),
        ROW('col1c' AS col1, 'col2b' AS col2)
    ),
    -- Creation of a unique index on col1
    base_index_u := INDEX_UNIQUE(base BY col1)
RETURN
    -- Key does not exist
    value_u0 := ELEMENT_AT_KEY(base_index_u, 'col1d'),
    -- Key exists
    value_u1 := ELEMENT_AT_KEY(base_index_u, 'col1a')
;

INDEX_UNIQUE() returns INDEX(<key_type>). Each key maps to exactly one row. If the field has duplicates, only the last row for each key is kept. INDEX is a FeatureQL type, not a SQL MAP.

Multi index: multiple rows per key

When the field may have duplicates (like a category or status), INDEX_MULTI() maps each key to an array of matching rows. Use ELEMENTS_AT_KEY() (plural) to retrieve all rows for a given key.

DEFINE
    base := ARRAY(
        ROW('col1a' AS col1, 'col2a' AS col2),
        ROW('col1b' AS col1, 'col2b' AS col2),
        ROW('col1c' AS col1, 'col2b' AS col2)
    ),
    -- Creation of a multi index on col2
    base_index_m := INDEX_MULTI(base BY col2)
RETURN
    -- Key does not exist
    value_m0 := ELEMENTS_AT_KEY(base_index_m, 'col2d'),
    -- Key exists
    value_m1 := ELEMENTS_AT_KEY(base_index_m, 'col2b')
;

INDEX_MULTI() also returns INDEX(<key_type>), but each key maps to an array of matching rows rather than a single row.

When to use each approach

Indexes pay off when you look up multiple keys against the same array — the index is built once and reused for each lookup.