Skip to main content

try_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, or NULL if an intermediate value has an incompatible type or the target is not an array.

Syntax​

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

SQL NULL arguments return SQL NULL.

The function returns SQL NULL for incompatible intermediate types or a non-array target. To raise an error instead, use 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 try_variant_array_append(parse_json('[1]'), '$', 2)
[1,2]

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

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

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

-- Invalid path
> SELECT try_variant_array_append(parse_json('[]'), 'invalid', 1)
Error: INVALID_VARIANT_PATH.