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

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

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