In MariaDB, JSON_TYPE() is a built-in function that returns the type of a JSON value, as a string. 

Syntax

The syntax goes like this:

JSON_TYPE(json_val)

Where json_val is the value for which to return the type.

Object Types

The possible return types are listed in the following table:

Return type	Value	Example
ARRAY	JSON array.	[1, 2, 3]
OBJECT	JSON object.	{"a":"1"}
BOOLEAN	JSON true/false literal.	true or false
DOUBLE	A number with at least one floating point decimal.	1.2
INTEGER	A number without a floating point decimal.	1
NULL	JSON null literal. This is returned as a string, and is not to be confused with the SQL NULL value.	null
STRING	JSON string.	"bird"

Example

Here’s an example to demonstrate.

SET @json = '
    { 
        "name" : "Fluffy", 
        "type" : "Cat" 
    }
';

SELECT JSON_TYPE(@json);

Result:

+------------------+
| JSON_TYPE(@json) |
+------------------+
| OBJECT           |
+------------------+

In this example I passed a whole JSON document, which has a type of OBJECT.

Here are more examples:

SELECT 
    JSON_TYPE('[1, 2, 3]'),
    JSON_TYPE('{ "a" : 1 }'),
    JSON_TYPE('true'),
    JSON_TYPE('false'),
    JSON_TYPE(10.59),
    JSON_TYPE(10),
    JSON_TYPE(null),
    JSON_TYPE('"Fuzzy Smith"');

Result (using vertical output):

    JSON_TYPE('[1, 2, 3]'): ARRAY
  JSON_TYPE('{ "a" : 1 }'): OBJECT
         JSON_TYPE('true'): BOOLEAN
        JSON_TYPE('false'): BOOLEAN
          JSON_TYPE(10.59): DOUBLE
             JSON_TYPE(10): INTEGER
           JSON_TYPE(null): NULL
JSON_TYPE('"Fuzzy Smith"'): STRING

Extract the Value from a JSON Document

In the previous examples, I passed each value directly to the function as a literal.

We can combine JSON_TYPE() with other functions, such as JSON_EXTRACT() to find out the type of a value within a larger JSON document. Here’s an example of extracting a value from a JSON document in order to find out its type:

SET @json = '
    { 
        "name" : "Wag", 
        "scores" : [8, 0, 9] 
    }
';

SELECT 
    JSON_TYPE(JSON_EXTRACT(@json, '$.name')) AS Result;

Result:

+--------+
| Result |
+--------+
| STRING |
+--------+

Here’s another example that returns more types:

SET @json = '
    { 
        "name" : "Wag", 
        "scores" : [8, 0, 9],
        "weight" : 10.50,
        "height" : null,
        "age" : 4
    }
';

SELECT 
    JSON_TYPE(
        JSON_EXTRACT(@json, '$.name')
        ) AS a,
    JSON_TYPE(
        JSON_EXTRACT(@json, '$.scores')
        ) AS b,
    JSON_TYPE(
        JSON_EXTRACT(@json, '$.scores[0]')
        ) AS c,
    JSON_TYPE(
        JSON_EXTRACT(@json, '$.weight')
        ) AS d,
    JSON_TYPE(
        JSON_EXTRACT(@json, '$.height')
        ) AS e,
    JSON_TYPE(
        JSON_EXTRACT(@json, '$.age')
        ) AS f;

Result:

+--------+-------+---------+--------+------+---------+
| a      | b     | c       | d      | e    | f       |
+--------+-------+---------+--------+------+---------+
| STRING | ARRAY | INTEGER | DOUBLE | NULL | INTEGER |
+--------+-------+---------+--------+------+---------+

Null Argument

If the argument is null, then null is returned.

SELECT JSON_TYPE(null);

Result:

+-----------------+
| JSON_TYPE(null) |
+-----------------+
| NULL            |
+-----------------+

Incorrect Parameter Count

Calling JSON_TYPE() without an argument results in an error:

SELECT JSON_TYPE();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_TYPE'

It’s the same when too many arguments are passed:

SELECT JSON_TYPE(1, 2);

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_TYPE'