Return the element at a given array position or the value for a given map key.
ELEMENT_AT(collection, key)## Overview Returns the element at the specified position in an array or the value for the specified key in a map. ELEMENT_AT is the canonical polymorphic accessor for both collection types and is the safer alternative to bracket indexing because out-of-bounds or missing-key access returns NULL rather than raising. Typical uses include reading a known position from an ordered array column, looking up a named attribute in a sidecar map, and implementing negative-index access (for example to obtain the last element). ## Behavior - For arrays, returns the element at a 1-based position. Negative positions count from the end (-1 is the last element). - For maps, returns the value associated with the given key. - Returns NULL when the array index is out of bounds or the map key is absent; it does not raise. - Element or value type matches the collection's element or value type. - Runs in O(1) for arrays and O(log n) or O(1) for maps depending on the internal representation. - Never mutates the input. ## Null and empty handling - NULL input collection returns NULL. - NULL key argument on a map returns NULL (a NULL key cannot be stored in a map in this engine). - NULL index on an array returns NULL. - Array index outside the range [-ARRAY_SIZE, -1] union [1, ARRAY_SIZE] returns NULL; zero index returns NULL. - Missing map key returns NULL; the function does not distinguish "key absent" from "value is NULL". ## Compatibility - Matches the array/map SQL convention for ELEMENT_AT with safe (NULL-returning) bounds behaviour. Bracket indexing (arr[i] or m[k]) is a commonly-accepted synonym. - Engines that raise on out-of-bounds access instead of returning NULL provide a separate indexed-access operator; this engine's ELEMENT_AT is always the safe form.
| Name | Type | Description |
|---|---|---|
collection | Specifies the collection to access. Accepts an ARRAY (indexed by position) or a MAP (indexed by key). | |
key | Specifies the 1-based array index or the map key. Negative indices on arrays count from the end (-1 is the last element). |
-- Array access by 1-based position
SELECT ELEMENT_AT(ARRAY[10, 20, 30], 2); -- 20-- Negative index counts from the end
SELECT ELEMENT_AT(ARRAY[10, 20, 30], -1); -- 30-- Map access by key
SELECT ELEMENT_AT(MAP('a', 1, 'b', 2), 'b'); -- 2-- Missing map key returns NULL
SELECT ELEMENT_AT(MAP('a', 1), 'z'); -- NULL-- Out-of-bounds array index returns NULL
SELECT ELEMENT_AT(ARRAY[1, 2, 3], 5); -- NULL-- Access the third event in a per-session trail
SELECT session_id, ELEMENT_AT(events, 3) AS third_event
FROM analytics.events.user_sessions;