The JSON_TYPE function in MySQL is used to determine the type of a given JSON value. It takes a JSON value as an argument and returns a string that represents the type of the value. Valid JSON values include objects, arrays, scalar types (e.g., integers, strings, booleans), and null values.
Syntax
JSON_TYPE(json_val)
Where:
json_val is the JSON value for which to determine the type. This can be an object, an array, a scalar type, or null.
Possible Return Values:
OBJECT: JSON object
ARRAY: JSON array
BOOLEAN: JSON boolean
NULL: JSON null value
INTEGER: MySQL TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT types
DOUBLE: MySQL DOUBLE and FLOAT types
DECIMAL: MySQL DECIMAL and NUMERIC types
DATE: MySQL DATETIME, TIME and TIMESTAMP types
STRING: MySQL CHAR, VARCHAR, TEXT, ENUM, and SET types
BLOB: MySQL BINARY, VARBINARY, BLOB, and BIT types
Examples:
SELECT JSON_TYPE('{"name": "John Doe", "age": 30}');
This will return OBJECT.
SELECT JSON_TYPE('["apple", "banana", "orange"]');
This will return ARRAY.
SELECT JSON_TYPE(false);
This will return BOOLEAN.
SELECT JSON_TYPE('');
This will return NULL.
SELECT JSON_TYPE(123);
This will return INTEGER.
SELECT JSON_TYPE("Hello, world!");
This will return STRING.
The JSON_TYPE() function is a powerful tool for working with JSON data in MySQL. It can be used to extract, manipulate, and filter JSON data, and it can be used in conjunction with other MySQL JSON functions to perform complex data analysis tasks.