ELEMENT_AT

​

Syntax

​

Scalar

element_at(collection, index_or_key)

​

Arguments

• T is the array element type: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT.
• V is the map value type: BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT.

​

Returns

• Type: T for ARRAY; V for MAP
• NULL behavior: NULL collection, NULL index/key or missing map key returns NULL. Out-of-bounds arrays return NULL for TRY_ELEMENT_AT; ELEMENT_AT raises an error when ANSI mode is enabled, otherwise returns NULL.
• Shape: One selected value per input row. For ARRAY<T>, the result has the element type and shape T; for MAP<K,V>, it has the value type and shape V. If that selected type is ARRAY, MAP or STRUCT, its nested contents and type are retained while the enclosing lookup collection is removed.
• Ordering: A returned nested ARRAY retains its element order, a returned MAP retains its entry order without adding a SQL key-order guarantee, and a returned STRUCT retains its field order. This scalar lookup does not impose SQL output row ordering.

​

Properties

​

Usage notes

• E6 and Databricks function-call indexing is one-based. Negative positions count from the end. Snowflake source context currently uses zero-based indexing.
• Index zero in a one-based ARRAY function call is invalid, including TRY_ELEMENT_AT; TRY only suppresses bounds failures.
• A nested ARRAY/MAP/STRUCT value is selected as a value of that type; this lookup does not flatten or re-sort its nested contents.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• Deterministic: The same inputs and settings produce the same result.

​

Examples

​

Typical behavior

• E6/Databricks source-dialect indexing.

SELECT element_at(ARRAY(10, 20, 30), 1) AS first_value, element_at(ARRAY(10, 20, 30), -1) AS last_value;

​

Look up map keys

SELECT element_at(map('red', 10, 'blue', 20), 'blue') AS found, element_at(map('red', 10, 'blue', 20), 'green') AS missing;

​

Limitations

• A list encoded with key/value or keys/values fields and a nonnumeric key is routed to map lookup; a numeric key remains positional.

​

Related functions

• get