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 |
    +----------------------------------------------------------------+