ELEMENT_AT

Function

The ELEMENT_AT function is used to extract the element value from an array, map, struct, or variant 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 STRUCT, it returns the subfield at the specified position (starting from 1) or with the specified field name, equivalent to STRUCT_ELEMENT.
• When applied to a VARIANT, it returns the value of the specified subfield.

Syntax

ELEMENT_AT(container, key_or_index)

Parameters

• container: Can be ARRAY, MAP, STRUCT, or VARIANT.
• key_or_index:
  • For ARRAY: An integer, with indexing starting from 1.
  • For MAP: The key type (K) of the MAP, which can be any supported primitive type.
  • For STRUCT: A constant integer field position (starting from 1) or a constant string field name (matched case-insensitively).
  • For VARIANT: A string type.

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 STRUCT: Returns the specified subfield value.
• For VARIANT: Returns a VARIANT type value.
• If the index or key does not exist, returns NULL (for STRUCT, an out-of-bound position or a non-existent field name reports an error).
• If the parameter is NULL, returns NULL.

Notes

1. Array indexes start from 1, not 0.
2. Negative indexes are supported: -1 represents the last element, -2 the second-to-last, and so on.
3. The ELEMENT_AT(container, key_or_index) function behaves the same as container[key_or_index] (see examples for details).

Examples

1. 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 |
   +--------------+

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 |
   +--------------------------+

3. Accessing a non-existent KEY in a MAP returns NULL.

   SELECT ELEMENT_AT({"a": 1, "b": 2}, "c");
   +-----------------------------------+
   | ELEMENT_AT({"a": 1, "b": 2}, "c") |
   +-----------------------------------+
   |                              NULL |
   +-----------------------------------+

4. Accessing a STRUCT subfield by position or by field name (equivalent to STRUCT_ELEMENT).

   SELECT ELEMENT_AT(NAMED_STRUCT('name', 'Jack', 'id', 1728923), 1);
   +------------------------------------------------------------+
   | ELEMENT_AT(NAMED_STRUCT('name', 'Jack', 'id', 1728923), 1) |
   +------------------------------------------------------------+
   | Jack                                                       |
   +------------------------------------------------------------+
   
   SELECT NAMED_STRUCT('name', 'Jack', 'id', 1728923)['id'];
   +--------------------------------------------------+
   | NAMED_STRUCT('name', 'Jack', 'id', 1728923)['id'] |
   +--------------------------------------------------+
   |                                          1728923 |
   +--------------------------------------------------+

5. When accessing a subfield of a VARIANT, if the VARIANT 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), "") |
   +----------------------------------------+
   |                                        |
   +----------------------------------------+