In MySQL, the JSON_TYPE() function returns the type of a JSON value. More specifically, it returns a utf8mb4 string indicating the value’s type.

You provide the JSON value as an argument.

Syntax

The syntax goes like this:

JSON_TYPE(json_val)

Where json_val is the JSON value for which to return the type. This can be an object, an array, or a scalar type.

Example 1 – Object

Here’s an example when the value is an object.

SET @data = '{"Name": "Homer", "Stupid": true}';
SELECT JSON_TYPE(@data) 'JSON Type';

Result:

+-----------+
| JSON Type |
+-----------+
| OBJECT    |
+-----------+

Example 2 – Array

Here’s an array example.

SET @data = '[1, 2, 3]';
SELECT JSON_TYPE(@data) 'JSON Type';

Result:

+-----------+
| JSON Type |
+-----------+
| ARRAY     |
+-----------+

Example 3 – Extract Data from an Array

You can also get the type of the individual elements in the array.

SET @data = '[1, 2, 3]';
SELECT 
  JSON_EXTRACT(@data, '$[1]') 'Data',
  JSON_TYPE(JSON_EXTRACT(@data, '$[1]')) 'JSON Type';

Result:

+------+-----------+
| Data | JSON Type |
+------+-----------+
| 2    | INTEGER   |
+------+-----------+

Example 4 – Extract Data from an Object

The same applies to members of an object.

SET @data = '{"Name": "Homer", "Stupid": "True"}';
SELECT 
  JSON_EXTRACT(@data, '$.Stupid') 'Data',
  JSON_TYPE(JSON_EXTRACT(@data, '$.Stupid')) 'JSON Type';

Result:

+--------+-----------+
| Data   | JSON Type |
+--------+-----------+
| "True" | STRING    |
+--------+-----------+

In that case, the value was the string literal "True".

Here’s another example, but this time using the boolean value true.

SET @data = '{"Name": "Homer", "Stupid": true}';
SELECT 
  JSON_EXTRACT(@data, '$.Stupid') 'Data',
  JSON_TYPE(JSON_EXTRACT(@data, '$.Stupid')) 'JSON Type';

Result:

+------+-----------+
| Data | JSON Type |
+------+-----------+
| true | BOOLEAN   |
+------+-----------+

Example 5 – Database Query

This example uses a database column.

SELECT 
  Contents,
  JSON_TYPE(Contents) 'Contents',
  JSON_TYPE(JSON_EXTRACT(Contents, '$.Name')) 'Name'
FROM Collections 
WHERE CollectionId = 4;

Result:

+--------------------------------+----------+--------+
| Contents                       | Contents | Name   |
+--------------------------------+----------+--------+
| {"Name": "Homer", "Stupid": 1} | OBJECT   | STRING |
+--------------------------------+----------+--------+