top of page
CerebroSQL

MySQL: 

JSON_TYPE

Syntax:
JSON_TYPE(json_val)

Returns a utf8mb4 string indicating the type of a JSON value. This can
be an object, an array, or a scalar type, as shown here:

mysql> SET @j = '{"a": [10, true]}';
mysql> SELECT JSON_TYPE(@j);
+---------------+
| JSON_TYPE(@j) |
+---------------+
| OBJECT |
+---------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a'));
+------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@j, '$.a')) |
+------------------------------------+
| ARRAY |
+------------------------------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]'));
+---------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]')) |
+---------------------------------------+
| INTEGER |
+---------------------------------------+
mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]'));
+---------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]')) |
+---------------------------------------+
| BOOLEAN |
+---------------------------------------+

JSON_TYPE() returns NULL if the argument is NULL:

mysql> SELECT JSON_TYPE(NULL);
+-----------------+
| JSON_TYPE(NULL) |
+-----------------+
| NULL |
+-----------------+

An error occurs if the argument is not a valid JSON value:

mysql> SELECT JSON_TYPE(1);
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.

For a non-NULL, non-error result, the following list describes the
possible JSON_TYPE() return values:

o Purely JSON types:

o OBJECT: JSON objects

o ARRAY: JSON arrays

o BOOLEAN: The JSON true and false literals

o NULL: The JSON null literal

o Numeric types:

o INTEGER: MySQL TINYINT, SMALLINT, MEDIUMINT and INT and BIGINT
scalars

o DOUBLE: MySQL DOUBLE FLOAT scalars

o DECIMAL: MySQL DECIMAL and NUMERIC scalars

o Temporal types:

o DATETIME: MySQL DATETIME and TIMESTAMP scalars

o DATE: MySQL DATE scalars

o TIME: MySQL TIME scalars

o String types:

o STRING: MySQL utf8 character type scalars: CHAR, VARCHAR, TEXT,
ENUM, and SET

o Binary types:

o BLOB: MySQL binary type scalars including BINARY, VARBINARY, BLOB,
and BIT

o All other types:

o OPAQUE (raw bits)

URL: https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html

Example

bottom of page