MySQL JSON_DEPTH() Function

In MySQL, the JSON_DEPTH() function returns the maximum depth of a JSON document.

JSON_DEPTH() Syntax

Here is the syntax of the MySQL JSON_DEPTH() function:

JSON_DEPTH(json)

Parameters

json
Required. A JSON document which depth to calculate.

Return value

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

JSON_DEPTH() calculates depth according to the following rules:

  • The depth of an empty array, or an empty object, or a scalar is 1.
  • The depth of an nonempty array containing only elements of depth 1 is 2.
  • The depth of an nonempty object containing only member values of depth 1 is 2.

This function will return NULL if the argument is NULL.

MySQL will give an error if the parameter is not a valid JSON document. You can use JSON_VALID() to verify the JSON document.

JSON_DEPTH() Examples

Here are some examples of JSON_DEPTH().

Example 1

SELECT JSON_DEPTH('[]'), JSON_DEPTH('[1, 2]'), JSON_DEPTH('[1, [2, 3]]');
+------------------+----------------------+---------------------------+
| JSON_DEPTH('[]') | JSON_DEPTH('[1, 2]') | JSON_DEPTH('[1, [2, 3]]') |
+------------------+----------------------+---------------------------+
|                1 |                    2 |                         3 |
+------------------+----------------------+---------------------------+

Example 2

SELECT JSON_DEPTH('{}'), JSON_DEPTH('{"x": 1}'), JSON_DEPTH('{"x": {"y": 1}}');
+------------------+------------------------+-------------------------------+
| JSON_DEPTH('{}') | JSON_DEPTH('{"x": 1}') | JSON_DEPTH('{"x": {"y": 1}}') |
+------------------+------------------------+-------------------------------+
|                1 |                      2 |                             3 |
+------------------+------------------------+-------------------------------+