Skip to main content

variant_delete function

Applies to: check marked yes Databricks SQL check marked yes 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: A VARIANT expression.
  • path1, path2, ...: One or more STRING expressions 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}