Categories: Variant Type, Datatype

IS_VARIANT_NULL Preview

Returns TRUE if the input expression is a VARIANT<NULL> (as opposed to SQL NULL).

Syntax

IS_VARIANT_NULL(variant VARIANT) → BOOLEAN

• variant: The VARIANT value to test.

Examples

IS_VARIANT_NULL example

SELECT IS_VARIANT_NULL(PARSE_JSON('null'))
-- TRUE

IS_VARIANT_NULL example

SELECT IS_VARIANT_NULL(PARSE_JSON('"hello"'))
-- FALSE

IS_VARIANT_NULL example

SELECT IS_VARIANT_NULL(VARIANT_GET(PARSE_JSON('{"name": null}'), '$.name'))
-- TRUE

Usage Notes

VARIANT null vs SQL NULL

When working with VARIANT data, there are two distinct null concepts:

• VARIANT null: A null value inside a VARIANT, created when parsing JSON that contains an explicit null (e.g., {"age": null} or just null)
• SQL NULL: The absence of a value (e.g., a missing path or TO_VARIANT(NULL))

IS_VARIANT_NULL returns TRUE only for VARIANT null values. For SQL NULL, it returns NULL (not FALSE).

Distinguishing null types

Scenario	IS NULL	IS_VARIANT_NULL
Path exists, value is VARIANT null	FALSE	TRUE
Path exists, has value	FALSE	FALSE
Path does not exist (SQL NULL)	TRUE	NULL

Checking for missing or null values

To get a clean boolean for "field is missing or VARIANT null", use COALESCE:

SELECT 
  COALESCE(IS_VARIANT_NULL(VARIANT_GET(payload, '$.amount')), TRUE) AS amount_missing
FROM events;

This returns TRUE if the path is missing (SQL NULL) or contains VARIANT null, and FALSE if the path exists with a value.