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.
Databricks raises INVALID_INDEX_OF_ZERO if index is 0.
Databricks raises COLLECTION_SIZE_LIMIT_EXCEEDED if the result exceeds the array size limit.
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.
Common error conditions
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: INVALID_INDEX_OF_ZERO
> 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"]