element_at
function
Applies to: Databricks SQL Databricks Runtime
Returns the element of an arrayExpr
at index
.
Returns the value of mapExpr
for key
.
Arguments
arrayExpr
: An ARRAY expression.index
: An INTEGER expression.mapExpr
: A MAP expression.key
: An expression matching the type of the keys ofmapExpr
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