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> )
Aliasβ
JSONB_TYPE
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_object>
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β
-
JSON is of string type:
SELECT JSON_TYPE('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------------------+
| jsonb_type(cast('{"name": "John", "age": 30}' as JSON), '$.name') |
+-------------------------------------------------------------------+
| string |
+-------------------------------------------------------------------+ -
JSON is of number type:
SELECT JSON_TYPE('{"name": "John", "age": 30}', '$.age');
+------------------------------------------------------------------+
| jsonb_type(cast('{"name": "John", "age": 30}' as JSON), '$.age') |
+------------------------------------------------------------------+
| int |
+------------------------------------------------------------------+ -
NULL parameters
select json_type(NULL, '$.key1');
+---------------------------+
| json_type(NULL, '$.key1') |
+---------------------------+
| NULL |
+---------------------------+ -
NULL parameters 2
select json_type('{"key1": true}', NULL);
+-----------------------------------+
| json_type('{"key1": true}', NULL) |
+-----------------------------------+
| NULL |
+-----------------------------------+ -
Field specified by
json_path
parameter does not existselect json_type('{"key1": true}', '$.key2');
+---------------------------------------+
| json_type('{"key1": true}', '$.key2') |
+---------------------------------------+
| NULL |
+---------------------------------------+ -
Invalid
json_path
parameterselect json_type('{"key1": true}', '$.');
ERROR 1105 (HY000): errCode = 2, detailMessage = [INVALID_ARGUMENT]Json path error: Invalid Json Path for value: $.