array_insert function
Applies to: 
Databricks SQL 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 insertelem. If index is negativeelemis inserted relative to the end of the array.elem: An expression of the same type as the elements ofarray.
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
SQL
> 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"]