Skip to main content

variant_strip_nulls function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 19 and above

Returns variantExpr with variant null fields recursively removed from objects and, optionally, variant null elements removed from arrays.

Syntax​

SQL
variant_strip_nulls ( variantExpr [, include_arrays] )

Arguments​

  • variantExpr: A VARIANT expression.
  • include_arrays: An optional constant (foldable) BOOLEAN expression. If true, removes variant null elements from arrays. The default is true.

Returns​

A value of type VARIANT.

Variant null fields in objects inside arrays are removed even when include_arrays is false.

Empty objects and arrays, scalars, and top-level variant nulls are preserved.

SQL NULL arguments return SQL NULL. Variant nulls, such as parse_json('null'), are distinct from SQL NULL. See is_variant_null.

Examples​

SQL
-- Remove null object fields
> SELECT variant_strip_nulls(parse_json('{"a": 1, "b": null, "c": 3}'))
{"a":1,"c":3}

-- Remove null array elements
> SELECT variant_strip_nulls(parse_json('[1, null, 3]'))
[1,3]

-- Preserve null array elements
> SELECT variant_strip_nulls(parse_json('[{"a": 1, "b": null}, null]'), false)
[{"a":1},null]

-- Preserve an empty object
> SELECT variant_strip_nulls(parse_json('{"a": {"b": null}}'))
{"a":{}}

-- Variant null input
> SELECT variant_strip_nulls(parse_json('null'))
null

-- SQL NULL input
> SELECT variant_strip_nulls(CAST(NULL AS VARIANT))
NULL