array_insert function

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

Returns an expanded array where elem is inserted at the index position.

Syntax 

array_insert(array, index, elem)

Arguments

  • array: An ARRAY.

  • index: A non-zero INTEGER expression specifying where to insert elem. If index is negative elem is inserted relative to the end of the array.

  • elem: An expression of the same type as the elements of array.

Returns

An ARRAY of the same type as array.

Notes

All elements starting with index are shifted by one position to make space for elem at index. If index is outside the cardinality of array the array is padded with NULLs.

Examples 

> SELECT array_insert(array('a', 'b', 'c'), 1, 'z');
 ["z","a","b","c"]

> SELECT array_insert(array('a', 'b', 'c'), 0, 'z');
 Error

> SELECT array_insert(array('a', 'b', 'c'), -1, 'z');
 ["a","b","c","z"]

> SELECT array_insert(array('a', 'b', 'c'), 5, 'z');
 ["a","b","c",NULL,"z"]

> SELECT array_insert(array('a', 'b', 'c'), -5, 'z');
 ["z",NULL,"a","b","c"]

> SELECT array_insert(array('a', 'b', 'c'), 2, cast(NULL AS STRING));
 ["a",NULL,"b","c"]