MySQL provides us with a wide range of functions for working with JSON documents. Below are four MySQL functions that we can use to get information about JSON values.

The JSON_DEPTH() Function

The JSON_DEPTH() function returns the maximum dept of a given JSON document.

Example:

SELECT JSON_DEPTH('[7, {"name": "Homer"}]');

Result:

3

Here’s another example:

SELECT JSON_DEPTH('[7, {"scores": [3, 7, 4]}]');

Result:

4

The depth increased by 1, because I added an array to the document.

The JSON_LENGTH() Function

The JSON_LENGTH() function returns the length of a JSON document, or, if a path argument is given, the length of the value within the document identified by the path.

Example:

SELECT JSON_LENGTH('{"name": "Fluffy", "age": 3}');

Result:

2

Here’s an example that uses the optional path argument:

SET @data = '{  
    "Person": {    
       "Name": "Homer", 
       "Age": 39,
       "Hobbies": ["Eating", "Sleeping"]  
    }
 }';
SELECT JSON_LENGTH(@data, '$.Person') 'Result';

Result:

3

The JSON_TYPE() Function

The JSON_TYPE() function returns a utf8mb4 string indicating the type of a JSON value.

Example:

SELECT JSON_TYPE('{"name": "Fluffy", "age": 3}');

Result:

OBJECT

And for another example, here’s an array:

SELECT JSON_TYPE('[1,2,3]');

Result:

ARRAY

The JSON_VALID() Function

The JSON_VALID() function returns 0 or 1 to indicate whether a value is valid JSON.

Example:

SELECT JSON_VALID('{"name": "Fluffy", "age": 3}');

Result:

1

In this case the value is valid JSON.

Here’s an invalid value:

SELECT JSON_VALID('{"name"}');

Result:

0