variant_delete function
Applies to: Databricks SQL
Databricks Runtime 19 and above
Returns variantExpr with the fields or array elements at the specified paths removed.
Syntax
SQL
variant_delete ( variantExpr, path1 [, path2, ...] )
Arguments
variantExpr: AVARIANTexpression.path1,path2, ...: One or moreSTRINGexpressions with well-formed JSON path expressions specifying the paths to remove. Array indexes are zero-based.
Returns
A value of type VARIANT.
Paths are applied from left to right to the result of each preceding deletion. Deleting an array element shifts subsequent elements left.
Missing fields, out-of-range indexes, and incompatible intermediate types are ignored. Empty objects and arrays are preserved.
If variantExpr is SQL NULL, the result is SQL NULL. SQL NULL paths are skipped.
Malformed paths or the root path $ raise INVALID_VARIANT_PATH.
Common error conditions
Examples
SQL
-- Remove object fields
> SELECT variant_delete(parse_json('{"a": 1, "b": 2, "c": 3}'), '$.a', '$.c')
{"b":2}
-- Remove an array element
> SELECT variant_delete(parse_json('[1, 2, 3]'), '$[1]')
[1,3]
-- Apply paths from left to right
> SELECT variant_delete(parse_json('[1, 2, 3]'), '$[0]', '$[0]')
[3]
-- Missing and incompatible paths
> SELECT variant_delete(parse_json('{"a": 1}'), '$.missing', '$.a.b')
{"a":1}
-- Preserve an empty object
> SELECT variant_delete(parse_json('{"a": {"b": 1}}'), '$.a.b')
{"a":{}}
-- Skip a SQL NULL path
> SELECT variant_delete(parse_json('{"a": 1, "b": 2}'), '$.a', CAST(NULL AS STRING))
{"b":2}