Skip to main content

try_variant_set function

Applies to: check marked yes Databricks SQL check marked yes 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: A VARIANT expression.
  • path: A STRING expression with a well-formed JSON path expression. Array indexes are zero-based.
  • val: An expression castable to VARIANT.
  • create_if_missing: An optional constant (foldable) BOOLEAN expression. If true, creates missing fields, array elements, and intermediate objects or arrays along path. The default is true.

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.