Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to:
Databricks SQL
Databricks Runtime 19 and above
Returns variantExpr with the fields or array elements at the specified paths removed.
Syntax
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
-- 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}