variant_set function
Applies to: Databricks SQL
Databricks Runtime 19 and above
Returns variantExpr with val inserted or set at path.
Syntax
variant_set ( variantExpr, path, val [, create_if_missing] )
Arguments
variantExpr: AVARIANTexpression.path: ASTRINGexpression with a well-formed JSON path expression. Array indexes are zero-based.val: An expression castable toVARIANT.create_if_missing: An optional constant (foldable)BOOLEANexpression. Iftrue, creates missing fields, array elements, and intermediate objects or arrays alongpath. The default istrue.
Returns
A value of type VARIANT.
Replacing an array element does not shift other elements.
If create_if_missing is true, missing fields and intermediate containers are created. An index equal to the array length appends val. A larger index pads the array with variant nulls before adding val.
If create_if_missing is false, a missing target or intermediate path leaves variantExpr unchanged.
SQL NULL arguments return SQL NULL.
Incompatible intermediate types raise VARIANT_PATH_TYPE_MISMATCH, regardless of create_if_missing. To return SQL NULL instead, use try_variant_set.
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
-- Replace an object field
> SELECT variant_set(parse_json('{"a": 1}'), '$.a', 2)
{"a":2}
-- Create a missing path
> SELECT variant_set(parse_json('{}'), '$.a.b', 1)
{"a":{"b":1}}
-- Replace an array element
> SELECT variant_set(parse_json('["a", "b", "c"]'), '$[1]', 'z')
["a","z","c"]
-- Append at the array length
> SELECT variant_set(parse_json('["a", "b", "c"]'), '$[3]', 'z')
["a","b","c","z"]
-- Missing path with creation disabled
> SELECT variant_set(parse_json('{"a": 1}'), '$.b', 2, false)
{"a":1}
-- Incompatible path
> SELECT variant_set(parse_json('{"a": 1}'), '$.a.b', 2)
Error: VARIANT_PATH_TYPE_MISMATCH.