try_variant_set function
Applies to: Databricks SQL
Databricks Runtime 19 and above
Returns variantExpr with val inserted or set at path, or NULL for incompatible intermediate types.
Syntax
SQL
try_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.
If create_if_missing is false, a missing target or intermediate path leaves variantExpr unchanged.
SQL NULL arguments return SQL NULL.
The function returns SQL NULL for incompatible intermediate types. To raise an error instead, use 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
SQL
-- Replace an object field
> SELECT try_variant_set(parse_json('{"a": 1}'), '$.a', 99)
{"a":99}
-- Incompatible path
> SELECT try_variant_set(parse_json('{"a": 1}'), '$.a.b', 2)
NULL
-- Missing path with creation disabled
> SELECT try_variant_set(parse_json('{"a": 1}'), '$.b', 2, false)
{"a":1}
-- Invalid root path
> SELECT try_variant_set(parse_json('{}'), '$', 1)
Error: INVALID_VARIANT_PATH.