Skip to main content

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.

Syntax​

SQL
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.

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​

SQL
-- 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.