variant_insert function
Applies to: Databricks SQL
Databricks Runtime 19 and above
Returns variantExpr with val inserted as a field or array element at path.
Syntax
SQL
variant_insert ( variantExpr, path, val )
Arguments
variantExpr: AVARIANTexpression.path: ASTRINGexpression with a well-formed JSON path expression. Array indexes are zero-based.val: An expression castable toVARIANT.
Returns
A value of type VARIANT.
Array insertion shifts subsequent elements right. An index equal to the array length appends val. A larger index pads the array with variant nulls before insertion.
Missing intermediate objects or arrays are created according to the next path segment.
SQL NULL arguments return SQL NULL.
Duplicate object fields raise VARIANT_DUPLICATE_KEY. Incompatible intermediate types raise VARIANT_PATH_TYPE_MISMATCH. To return SQL NULL instead, use try_variant_insert.
Malformed paths or the root path $ raise INVALID_VARIANT_PATH, and results exceeding the variant size limit raise VARIANT_SIZE_LIMIT.
Common error conditions
Examples
SQL
-- Insert an object field
> SELECT variant_insert(parse_json('{"a": 1}'), '$.b', 2)
{"a":1,"b":2}
-- Create a missing path
> SELECT variant_insert(parse_json('{}'), '$.a[0]', 1)
{"a":[1]}
-- Insert an array element
> SELECT variant_insert(parse_json('["a", "b", "c"]'), '$[1]', 'z')
["a","z","b","c"]
-- Pad an array with variant nulls
> SELECT variant_insert(parse_json('["a", "b"]'), '$[5]', 'z')
["a","b",null,null,null,"z"]
-- Insert a variant null
> SELECT variant_insert(parse_json('{"a": 1}'), '$.b', parse_json('null'))
{"a":1,"b":null}
-- SQL NULL argument
> SELECT variant_insert(parse_json('{"a": 1}'), '$.b', CAST(NULL AS VARIANT))
NULL
-- Duplicate object field
> SELECT variant_insert(parse_json('{"a": 1}'), '$.a', 2)
Error: VARIANT_DUPLICATE_KEY.