JSON_EXTRACT_BIGINT

Description

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

Syntax

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

Examples

1. Normal parameters
   SELECT json_extract_bigint('{"id": 122222222222223, "name": "doris"}', '$.id');

   +-------------------------------------------------------------------------+
   | json_extract_bigint('{"id": 122222222222223, "name": "doris"}', '$.id') |
   +-------------------------------------------------------------------------+
   |                                                         122222222222223 |
   +-------------------------------------------------------------------------+

2. Case where path does not exist
   SELECT json_extract_bigint('{"id": 122222222222223, "name": "doris"}', '$.id2');

   +--------------------------------------------------------------------------+
   | json_extract_bigint('{"id": 122222222222223, "name": "doris"}', '$.id2') |
   +--------------------------------------------------------------------------+
   |                                                                     NULL |
   +--------------------------------------------------------------------------+

3. NULL parameters
   SELECT json_extract_bigint('{"id": 122222222222223, "name": "doris"}', NULl);

   +-----------------------------------------------------------------------+
   | json_extract_bigint('{"id": 122222222222223, "name": "doris"}', NULl) |
   +-----------------------------------------------------------------------+
   |                                                                  NULL |
   +-----------------------------------------------------------------------+

   SELECT json_extract_bigint(NULL, '$.id2');

   +------------------------------------+
   | json_extract_bigint(NULL, '$.id2') |
   +------------------------------------+
   |                               NULL |
   +------------------------------------+

4. Case where conversion to BIGINT is not possible
   SELECT json_extract_bigint('{"id": 123, "name": "doris"}','$.name');

   +--------------------------------------------------------------+
   | json_extract_bigint('{"id": 123, "name": "doris"}','$.name') |
   +--------------------------------------------------------------+
   |                                                         NULL |
   +--------------------------------------------------------------+