Skip to main content

JSON_EXTRACT_DOUBLE

Description​

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

Syntax​

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

Examples​

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