Skip to main content

JSON_EXTRACT_BOOL

Description​

JSON_EXTRACT_BOOL extracts the field specified by <json_path> from a JSON object and converts it to BOOLEAN type.

Syntax​

JSON_EXTRACT_BOOL(<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(BOOLEAN) Returns the extracted BOOLEAN value, returns NULL in some cases

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> cannot be converted to BOOLEAN, returns NULL.
  4. Its behavior is consistent with "cast + json_extract", which is equivalent to:
    CAST(JSON_EXTRACT(<json_object>, <json_path>) as BOOLEAN)

Examples​

  1. Normal parameters
    SELECT json_extract_bool('{"id": true, "name": "doris"}', '$.id');
    +------------------------------------------------------------+
    | json_extract_bool('{"id": true, "name": "doris"}', '$.id') |
    +------------------------------------------------------------+
    | 1 |
    +------------------------------------------------------------+
  2. Case where path does not exist
    SELECT json_extract_bool('{"id": true, "name": "doris"}', '$.id2');
    +-------------------------------------------------------------+
    | json_extract_bool('{"id": true, "name": "doris"}', '$.id2') |
    +-------------------------------------------------------------+
    | NULL |
    +-------------------------------------------------------------+
  3. NULL parameters
    SELECT json_extract_bool('{"id": true, "name": "doris"}', NULl);
    +----------------------------------------------------------+
    | json_extract_bool('{"id": true, "name": "doris"}', NULl) |
    +----------------------------------------------------------+
    | NULL |
    +----------------------------------------------------------+
    SELECT json_extract_bool(NULL, '$.id2');
    +----------------------------------+
    | json_extract_bool(NULL, '$.id2') |
    +----------------------------------+
    | NULL |
    +----------------------------------+
  4. Case where conversion to BOOLEAN is not possible
    SELECT json_extract_bool('{"id": 123, "name": "doris"}','$.name');
    +------------------------------------------------------------+
    | json_extract_bool('{"id": 123, "name": "doris"}','$.name') |
    +------------------------------------------------------------+
    | NULL |
    +------------------------------------------------------------+