Categories:

Conditional Expression Functions , Semi-structured and 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 NULL. 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> )
Copy

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                           |
+--------------+-------+--------------------+--------------------------------+
Copy