try_element_at function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.4 LTS and above

Returns the element of an arrayExpr at index, or NULL if index is out of bound.

Returns the value of mapExpr for key, or NULL id key does not exist.

Syntax 

try_element_at(arrayExpr, index)

try_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 not be 0.

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

  • The function returns NULL if abs(index) exceeds the length of the array, or if key does not exist in the map.

Examples 

> SELECT try_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 try_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