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