ELEMENT_AT
Function
The ELEMENT_AT
function is used to extract the element value from an array or map based on the specified index or key.
- When applied to an ARRAY, it returns the element at the specified position.
- When applied to a MAP, it returns the value corresponding to the specified key.
- When applied to a VARIANT, it returns the value of the specified subfield.
Syntax
ELEMENT_AT(container, key_or_index)
Parameters
container
: Can beARRAY
,MAP
, orVARIANT
.key_or_index
:- For
ARRAY
: An integer, with indexing starting from 1. - For
MAP
: The key type (K
) of theMAP
, which can be any supported primitive type. - For
VARIANT
: A string type.
- For
Return Value
- For
ARRAY
: Returns the element at the specified index (T
type). - For
MAP
: Returns the value corresponding to the specified key (V
type). - For
VARIANT
: Returns aVARIANT
type value. - If the index or key does not exist, returns
NULL
. - If the parameter is
NULL
, returnsNULL
.
Notes
- Array indexes start from 1, not 0.
- Negative indexes are supported:
-1
represents the last element,-2
the second-to-last, and so on. - The
ELEMENT_AT(container, key_or_index)
function behaves the same ascontainer[key_or_index]
(see examples for details).
Examples
-
The
ELEMENT_AT
function works the same as[]
.SELECT ELEMENT_AT([1, 2, 3], 2);
+--------------------------+
| ELEMENT_AT([1, 2, 3], 2) |
+--------------------------+
| 2 |
+--------------------------+
SELECT [1, 2, 3][2];
+--------------+
| [1, 2, 3][2] |
+--------------+
| 2 |
+--------------+ -
Array indexing starts from 1; out-of-bounds access returns
NULL
.SELECT ELEMENT_AT([1, 2, 3], 0);
+--------------------------+
| ELEMENT_AT([1, 2, 3], 0) |
+--------------------------+
| NULL |
+--------------------------+
SELECT ELEMENT_AT([1, 2, 3], 4);
+--------------------------+
| ELEMENT_AT([1, 2, 3], 4) |
+--------------------------+
| NULL |
+--------------------------+ -
Accessing a non-existent KEY in a
MAP
returnsNULL
.SELECT ELEMENT_AT({"a": 1, "b": 2}, "c");
+-----------------------------------+
| ELEMENT_AT({"a": 1, "b": 2}, "c") |
+-----------------------------------+
| NULL |
+-----------------------------------+ -
When accessing a subfield of a
VARIANT
, if theVARIANT
value is not an OBJECT, an empty value is returned.SELECT ELEMENT_AT(CAST('{"a": 1, "b": 2}' AS VARIANT), "a");
+------------------------------------------------------+
| ELEMENT_AT(CAST('{"a": 1, "b": 2}' AS VARIANT), "a") |
+------------------------------------------------------+
| 1 |
+------------------------------------------------------+
SELECT ELEMENT_AT(CAST('123' AS VARIANT), "");
+----------------------------------------+
| ELEMENT_AT(CAST('123' AS VARIANT), "") |
+----------------------------------------+
| |
+----------------------------------------+