element_at function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

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 returns null.

Note

In Databricks Runtime, if spark.sql.ansi.failOnElementNotExists is false the function returns NULL instead of raising errors.

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 element_at(map(1, 'a', 2, 'b'), 3);
 NULL

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