Categories:
Conditional Expression Functions , Semi-structured Data Functions (Type Predicates)

IS_NULL_VALUE

Returns true if its VARIANT argument is a JSON null value.

Important

The JSON null value is distinct from the SQL NULL value.

This function returns true only for JSON null values, not SQL NULL values. The difference is shown in the first and third rows in the example below.

A missing JSON sub-column will be converted to a SQL NULL value, for which IS_NULL_VALUE returns false. The 4th column in the example below shows this.

Note that this function should not be confused with IS [ NOT ] NULL.

See also:
IS_<object_type>

Syntax

IS_NULL_VALUE( <variant_expr> )

Returns

The data type of the returned value is BOOLEAN.

Examples

This demonstrates the IS_NULL_VALUE function:

SELECT v, v:a, IS_NULL_VALUE(v:a), IS_NULL_VALUE(v:no_such_field)
    FROM
        (SELECT parse_json(column1) AS v
         FROM VALUES
             ('{"a": null}'),
             ('{"a": "foo"}'),
             (NULL)
        );
+--------------+-------+--------------------+--------------------------------+
| V            | V:A   | IS_NULL_VALUE(V:A) | IS_NULL_VALUE(V:NO_SUCH_FIELD) |
|--------------+-------+--------------------+--------------------------------|
| {            | null  | True               | NULL                           |
|   "a": null  |       |                    |                                |
| }            |       |                    |                                |
| {            | "foo" | False              | NULL                           |
|   "a": "foo" |       |                    |                                |
| }            |       |                    |                                |
| NULL         | NULL  | NULL               | NULL                           |
+--------------+-------+--------------------+--------------------------------+