Skip to main content

JSON_EXTRACT_LARGEINT

Description​

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

Syntax​

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

Examples​

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