Skip to main content

variant_array_append function

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

Returns variantExpr with val appended to the array at path.

Syntax​

SQL
variant_array_append ( variantExpr, path, val )

Arguments​

  • variantExpr: A VARIANT expression.
  • path: A STRING expression with a well-formed JSON path expression specifying the target array. Array indexes are zero-based.
  • val: An expression castable to VARIANT.

Returns​

A value of type VARIANT.

A missing path leaves variantExpr unchanged. val is appended as one element, so appending an array creates a nested array.

SQL NULL arguments return SQL NULL.

Incompatible intermediate types or a non-array target raise VARIANT_PATH_TYPE_MISMATCH. To return SQL NULL instead, use try_variant_array_append.

Malformed paths raise INVALID_VARIANT_PATH, and results exceeding the variant size limit raise VARIANT_SIZE_LIMIT.

Common error conditions​

Examples​

SQL
-- Append to a root array
> SELECT variant_array_append(parse_json('[1, 2, 3]'), '$', 4)
[1,2,3,4]

-- Append to a nested array
> SELECT variant_array_append(parse_json('{"a": [1, 2]}'), '$.a', 3)
{"a":[1,2,3]}

-- Append an array as a single element
> SELECT variant_array_append(parse_json('[1]'), '$', parse_json('[2, 3]'))
[1,[2,3]]

-- Missing path
> SELECT variant_array_append(parse_json('{"a": 1}'), '$.missing', 2)
{"a":1}

-- Non-array target
> SELECT variant_array_append(parse_json('{"a": 1}'), '$.a', 2)
Error: VARIANT_PATH_TYPE_MISMATCH.