variant_strip_nulls function
Applies to: Databricks SQL
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: AVARIANTexpression.include_arrays: An optional constant (foldable)BOOLEANexpression. Iftrue, removes variant null elements from arrays. The default istrue.
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