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.

WITH
    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 (SELECT * WHERE COL2='col2b' LIMIT 1)
    ),
    base_filtered_many := TRANSFORM(base USING (SELECT * WHERE COL2='col2b'))
SELECT
    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.

WITH
    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)
SELECT
    -- 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 a MAP(<key_type>, ROW(...)). Each key maps to exactly one row. If the field has duplicates, only the last row for each key is kept.

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.

WITH
    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)
SELECT
    -- 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() returns a MAP(<key_type>, ARRAY(ROW(...))). Each key maps to all rows that share that value.

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.