Skip to main content

variant_insert function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 19 and above

Returns variantExpr with val inserted as a field or array element at path.

Syntax​

SQL
variant_insert ( variantExpr, path, val )

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.

Returns​

A value of type VARIANT.

Array insertion shifts subsequent elements right. An index equal to the array length appends val. A larger index pads the array with variant nulls before insertion.

Missing intermediate objects or arrays are created according to the next path segment.

SQL NULL arguments return SQL NULL.

Duplicate object fields raise VARIANT_DUPLICATE_KEY. Incompatible intermediate types raise VARIANT_PATH_TYPE_MISMATCH. To return SQL NULL instead, use try_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 variant_insert(parse_json('{"a": 1}'), '$.b', 2)
{"a":1,"b":2}

-- Create a missing path
> SELECT variant_insert(parse_json('{}'), '$.a[0]', 1)
{"a":[1]}

-- Insert an array element
> SELECT variant_insert(parse_json('["a", "b", "c"]'), '$[1]', 'z')
["a","z","b","c"]

-- Pad an array with variant nulls
> SELECT variant_insert(parse_json('["a", "b"]'), '$[5]', 'z')
["a","b",null,null,null,"z"]

-- Insert a variant null
> SELECT variant_insert(parse_json('{"a": 1}'), '$.b', parse_json('null'))
{"a":1,"b":null}

-- SQL NULL argument
> SELECT variant_insert(parse_json('{"a": 1}'), '$.b', CAST(NULL AS VARIANT))
NULL

-- Duplicate object field
> SELECT variant_insert(parse_json('{"a": 1}'), '$.a', 2)
Error: VARIANT_DUPLICATE_KEY.