JSON_EXTRACT_ISNULL

Description

JSON_EXTRACT_ISNULL determines whether the field specified by <json_path> in a JSON object is a null value.

Syntax

JSON_EXTRACT_ISNULL(<json_object>, <json_path>)

Parameters

• <json_object>: JSON type, the target parameter to extract from.
• <json_path>: String type, the JSON path to extract the target element from the target JSON.

Return Value

Nullable(BOOL) Returns true if the value is null, otherwise returns false.

Usage Notes

1. If <json_object> or <json_path> is NULL, returns NULL.
2. If the element specified by <json_path> does not exist, returns NULL.
3. If the element specified by <json_path> is not null, returns false.

Examples

1. Normal parameters

   SELECT json_extract_isnull('{"id": 123, "name": "doris"}', '$.id');

   +-------------------------------------------------------------+
   | json_extract_isnull('{"id": 123, "name": "doris"}', '$.id') |
   +-------------------------------------------------------------+
   |                                                           0 |
   +-------------------------------------------------------------+

   SELECT json_extract_isnull('{"id": null, "name": "doris"}', '$.id');

   +--------------------------------------------------------------+
   | json_extract_isnull('{"id": null, "name": "doris"}', '$.id') |
   +--------------------------------------------------------------+
   |                                                            1 |
   +--------------------------------------------------------------+

2. Case where path does not exist

   SELECT json_extract_isnull('{"id": null, "name": "doris"}', '$.id2');

   +---------------------------------------------------------------+
   | json_extract_isnull('{"id": null, "name": "doris"}', '$.id2') |
   +---------------------------------------------------------------+
   |                                                          NULL |
   +---------------------------------------------------------------+

3. NULL parameters

   SELECT json_extract_isnull('{"id": 123, "name": "doris"}', NULl);

   +-----------------------------------------------------------+
   | json_extract_isnull('{"id": 123, "name": "doris"}', NULl) |
   +-----------------------------------------------------------+
   |                                                      NULL |
   +-----------------------------------------------------------+

   SELECT json_extract_isnull(NULL, '$.id2');

   +------------------------------------+
   | json_extract_isnull(NULL, '$.id2') |
   +------------------------------------+
   |                               NULL |
   +------------------------------------+