ARRAY_LOOKUP(...)

All functions > ARRAY > ARRAY_LOOKUP(...)

Returns the value corresponding to a lookup key using parallel key-value arrays.

Signatures

Returns: The value at the same position as the key

ARRAY_LOOKUP(keys: ARRAY<T>, lookup_key: T, values: ARRAY<U>) → U

Notes

• Canonical order is ARRAY_LOOKUP(keys, lookup_key, values) so the keys array is the receiver
• Key-first ARRAY_LOOKUP(lookup_key, keys, values) is a permissive habit; FORMAT (FIX) rewrites it
• Returns the value at the same index where the key is found
• Returns NULL if key is not found
• Only returns the first match if key appears multiple times
• Arrays must be same length

See also

• Array functions

Examples

RETURN
    -- Find value by key in parallel arrays
    f1 := ARRAY_LOOKUP(ARRAY('a', 'b', 'c'), 'b', ARRAY(10, 20, 30)),
    -- Returns NULL when key is not found
    f2 := ARRAY_LOOKUP(ARRAY('a', 'b', 'c'), 'x', ARRAY(10, 20, 30)),
    -- Numeric keys with string values
    f3 := ARRAY_LOOKUP(ARRAY(1, 2, 3), 2, ARRAY('first', 'second', 'third')),
    -- Returns first match when key appears multiple times
    f4 := ARRAY_LOOKUP(ARRAY('a', 'a', 'b'), 'a', ARRAY(1, 2, 3))
;