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
    INDEX_UNIQUE(BASE BY col1) AS BASE_INDEX_U,
SELECT
    -- Key does not exist
    ELEMENT_AT_KEY(BASE_INDEX_U, 'col1d') AS VALUE_U0,
    -- Key exists
    ELEMENT_AT_KEY(BASE_INDEX_U, 'col1a') AS VALUE_U1,
;

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
    INDEX_MULTI(BASE BY col2) AS BASE_INDEX_M,
SELECT
    -- Key does not exist
    ELEMENTS_AT_KEY(BASE_INDEX_M, 'col2d') AS VALUE_M0,
    -- Key exists
    ELEMENTS_AT_KEY(BASE_INDEX_M, 'col2b') AS VALUE_M1,
;

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.