Skip to main content

JSON_EXTRACT_INT

Description​

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

Syntax​

JSON_EXTRACT_INT(<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(INT) Returns the extracted INT 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 INT, returns NULL.
  4. Its behavior is consistent with "cast + json_extract", which is equivalent to:
    CAST(JSON_EXTRACT(<json_object>, <json_path>) as INT)

Examples​

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