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β
STRING JSON_TYPE( <JSON j> )
Aliasβ
- JSONB_TYPE
Required Parametersβ
| Parameter | Description |
|---|---|
<JSON j> | The JSON string to check the type of. |
Return Valueβ
Returns the type of the JSON string. Possible values include:
- "NULL": Indicates that the value in the JSON document is null.
- "BOOLEAN": Indicates that the value in the JSON document is of boolean type (true or false).
- "NUMBER": Indicates that the value in the JSON document is a number.
- "STRING": Indicates that the value in the JSON document is a string.
- "OBJECT": Indicates that the value in the JSON document is a JSON object.
- "ARRAY": Indicates that the value in the JSON document is a JSON array.
Usage Notesβ
JSON_TYPE returns the type of the outermost value in the JSON document. If the JSON document contains multiple different types of values, it will return the type of the outermost value. For invalid JSON strings, JSON_TYPE returns NULL. Refer to json tutorial
Examplesβ
- JSON is of string type:
SELECT JSON_TYPE('"Hello, World!"');
+------------------------------------------+
| JSON_TYPE('"Hello, World!"') |
+------------------------------------------+
| STRING |
+------------------------------------------+
- JSON is of number type:
SELECT JSON_TYPE('123');
+------------------------------------------+
| JSON_TYPE('123') |
+------------------------------------------+
| NUMBER |
+------------------------------------------+
- JSON is of null type:
SELECT JSON_TYPE('null');
+------------------------------------------+
| JSON_TYPE('null') |
+------------------------------------------+
| NULL |
+------------------------------------------+
