element_at function (Databricks SQL)

Returns the element of an arrayExpr at index.

Returns the value of mapExpr for key.

Syntax

element_at(arrayExpr, index)
element_at(mapExpr, key)

Arguments

  • arrayExpr: An ARRAY expression.

  • index: An INTEGER expression.

  • mapExpr: A MAP expression.

  • key: An expression matching the type of the keys of mapExpr

Returns

If the first argument is an ARRAY:

  • The result is of the type of the elements of expr.

  • abs(index) must be between 1 and the length of the array.

  • If index is negative the function accesses elements from the last to the first.

  • The function raises INVALID_ARRAY_INDEX_IN_ELEMENT_AT error if abs(index) exceeds the length of the array.

If the first argument is a MAP and key cannot be matched to an entry in mapExpr the function raises a MAP_KEY_DOES_NOT_EXIST error.

Examples

> SELECT element_at(array(1, 2, 3), 2);
 2

> SELECT try_element_at(array(1, 2, 3), 5);
 NULL

> SELECT element_at(array(1, 2, 3), 5);
 Error: INVALID_ARRAY_INDEX_IN_ELEMENT_AT

> SELECT element_at(map(1, 'a', 2, 'b'), 2);
 b

> SELECT try_element_at(map(1, 'a', 2, 'b'), 3);
 NULL

> SELECT element_at(map(1, 'a', 2, 'b'), 3);
 Error: MAP_KEY_DOES_NOT_EXIST