Purpose
JSON_DEPTH() returns the maximum depth of a JSON document.
Syntax
JSON_DEPTH(json_doc)
Description
json_doc specifies a JSON document. If the argument is NULL, NULL is returned.
If the argument is not a valid JSON document, an error is returned.
The depth of an empty array, empty object, or scalar value is 1. The depth of a non-empty array that contains only elements with a depth of 1 is 2. The depth of a non-empty object that contains only member values with a depth of 1 is 2. In other cases, the depth of a JSON document is greater than 2.
Examples
obclient> SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');
+------------------+------------------+--------------------+
| JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') |
+------------------+------------------+--------------------+
| 1 | 1 | 1 |
+------------------+------------------+--------------------+
1 row in set
obclient> SELECT JSON_DEPTH('[101, 202]'), JSON_DEPTH('[[], {}]');
+--------------------------+------------------------+
| JSON_DEPTH('[101, 202]') | JSON_DEPTH('[[], {}]') |
+--------------------------+------------------------+
| 2 | 2 |
+--------------------------+------------------------+
1 row in set
obclient> SELECT JSON_DEPTH('[101, {"a": 202}]');
+---------------------------------+
| JSON_DEPTH('[101, {"a": 202}]') |
+---------------------------------+
| 3 |
+---------------------------------+
1 row in set
obclient> SELECT JSON_DEPTH('{"a":101, "v":"aaa"}');
+------------------------------------+
| JSON_DEPTH('{"a":101, "v":"aaa"}') |
+------------------------------------+
| 2 |
+------------------------------------+
1 row in set