try_variant_insert function
Applies to: Databricks SQL
Databricks Runtime 19 and above
Returns variantExpr with val inserted at path, or NULL for duplicate object fields or incompatible intermediate types.
Syntax
SQL
try_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.
SQL NULL arguments return SQL NULL.
The function returns SQL NULL for duplicate object fields or incompatible intermediate types. To raise an error instead, use 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 try_variant_insert(parse_json('{"a": 1}'), '$.b', 2)
{"a":1,"b":2}
-- Duplicate object field
> SELECT try_variant_insert(parse_json('{"a": 1}'), '$.a', 2)
NULL
-- Incompatible path
> SELECT try_variant_insert(parse_json('{"a": 1}'), '$.a.b', 2)
NULL
-- Invalid root path
> SELECT try_variant_insert(parse_json('{}'), '$', 1)
Error: INVALID_VARIANT_PATH.