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β
- If
<json_object>
or<json_path>
is NULL, returns NULL. - If the element specified by
<json_path>
does not exist, returns NULL. - If the element specified by
<json_path>
is not null, returns false.
Examplesβ
-
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 |
+--------------------------------------------------------------+ -
Case where path does not exist
SELECT json_extract_isnull('{"id": null, "name": "doris"}', '$.id2');
+---------------------------------------------------------------+
| json_extract_isnull('{"id": null, "name": "doris"}', '$.id2') |
+---------------------------------------------------------------+
| NULL |
+---------------------------------------------------------------+ -
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 |
+------------------------------------+