variant_array_append function
Applies to: Databricks SQL
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: AVARIANTexpression.path: ASTRINGexpression with a well-formed JSON path expression specifying the target array. Array indexes are zero-based.val: An expression castable toVARIANT.
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.