MySQL JSON_TYPE() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_TYPE() function to get the type of a JSON value.

Introduction to MySQL JSON_TYPE() function

The JSON_TYPE() function is used to return the data type of a JSON value.

Here’s the syntax of the JSON_TYPE function:

JSON_TYPE(value)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• value: This is the JSON value that you want to get the type, which can be an object, an array, or a scalar type (integer, boolean, null, etc).

The JSON_TYPE() function returns a string that represents the JSON type of the value.

If the value is NULL, the JSON_TYPE() function returns NULL. If the value is not a valid JSON value, the function raises an error.

MySQL JSON_TYPE() function examples

Let’s take some examples of using the JSON_TYPE() function.

1) Getting the type of an object

SELECT 
  JSON_TYPE('{"name": "John"}') type;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| type   |
+--------+
| OBJECT |
+--------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

2) Getting the type of an array

SELECT 
  JSON_TYPE('[1,2,3]') type;Code language: SQL (Structured Query Language) (sql)

Output:

+-------+
| type  |
+-------+
| ARRAY |
+-------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) Getting the type of a string

SELECT 
  JSON_TYPE('"Hello"') type;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| type   |
+--------+
| STRING |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

4) Getting the type of a number

SELECT 
  JSON_TYPE('1.0') type;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| type   |
+--------+
| DOUBLE |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

5) Getting the type of a boolean

SELECT 
  JSON_TYPE('true') type;Code language: SQL (Structured Query Language) (sql)

Output:

+---------+
| type    |
+---------+
| BOOLEAN |
+---------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

6) Getting the type of NULL

SELECT 
  JSON_TYPE('null') type;Code language: SQL (Structured Query Language) (sql)

Output:

+------+
| type |
+------+
| NULL |
+------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the JSON_TYPE() function to get the type of a JSON value.