JSON_TYPE

Description

Used to determine the type of the field specified by json_path in the JSONB data. If the field does not exist, it returns NULL. If the field exists, it returns one of the following types:

• object
• array
• null
• bool
• int
• bigint
• largeint
• double
• string

Syntax

JSON_TYPE( <json>, <json_path> )

Parameters

• <json> The JSON string to check the type of.
• <json_path> String type, which specifies the location of the field in JSON. The path is usually given in $. At the beginning, use. to represent the hierarchical structure.

Return Value

Nullable<String>: Returns the type of the corresponding field.

Usage Notes

• If <json> or <json_path> is NULL, returns NULL.
• If <json_path> is not a valid path, the function reports an error.
• If the field specified by <json_path> does not exist, returns NULL.

Examples

1. JSON is of string type:

   SELECT JSON_TYPE('{"name": "John", "age": 30}', '$.name');

   +----------------------------------------------------+
   | JSON_TYPE('{"name": "John", "age": 30}', '$.name') |
   +----------------------------------------------------+
   | string                                             |
   +----------------------------------------------------+

2. JSON is of number type:

   SELECT JSON_TYPE('{"name": "John", "age": 30}', '$.age');

   +---------------------------------------------------+
   | JSON_TYPE('{"name": "John", "age": 30}', '$.age') |
   +---------------------------------------------------+
   | int                                               |
   +---------------------------------------------------+

3. JSON is of double type:

   select json_type('{"key1": 1234.44}', '$.key1');

   +------------------------------------------+
   | json_type('{"key1": 1234.44}', '$.key1') |
   +------------------------------------------+
   | double                                   |
   +------------------------------------------+

4. JSON is of bool type:

   select json_type('{"key1": true}', '$.key1');

   +---------------------------------------+
   | json_type('{"key1": true}', '$.key1') |
   +---------------------------------------+
   | bool                                  |
   +---------------------------------------+

5. NULL parameters

   select json_type(NULL, '$.key1');

   +---------------------------+
   | json_type(NULL, '$.key1') |
   +---------------------------+
   | NULL                      |
   +---------------------------+

6. NULL parameters 2

   select json_type('{"key1": true}', NULL);

   +-----------------------------------+
   | json_type('{"key1": true}', NULL) |
   +-----------------------------------+
   | NULL                              |
   +-----------------------------------+

7. Field specified by json_path parameter does not exist

   select json_type('{"key1": true}', '$.key2');

   +---------------------------------------+
   | json_type('{"key1": true}', '$.key2') |
   +---------------------------------------+
   | NULL                                  |
   +---------------------------------------+

8. Invalid json_path parameter

   select json_type('{"key1": true}', '$.');

   ERROR 1105 (HY000): errCode = 2, detailMessage = [INVALID_ARGUMENT]Json path error: Invalid Json Path for value: $.