MariaDB JSON_DEPTH() Function

In MariaDB, JSON_DEPTH() is a built-in function that returns the maximum depth of a given JSON document.

MariaDB JSON_DEPTH() syntax

Here is the syntax for the MariaDB JSON_DEPTH() function:

JSON_DEPTH(json)

Parameters

json

Required. A JSON document.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_DEPTH'.

Return value

MariaDB JSON_DEPTH() function returns the maximum depth of a JSON document. The JSON_DEPTH() function calculates the depth of the JSON document according to the following rules:

  • An empty array, empty object, or plain value has depth 1.
  • An array containing only elements of depth 1 has depth 2.
  • An object with depth 1 for all members has depth 2.
  • The depth of other JSON documents is greater than 2.

This function will return NULL if the argument is NULL.

If the argument is an invalid JSON, this function will return NULL.

MariaDB JSON_DEPTH() Examples

Here are some common examples to show the usages of the Mariadb JSON_DEPTH() function.

JSON array

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

Output:

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

JSON object

SELECT JSON_DEPTH('{}'), JSON_DEPTH('{"x": 1}'), JSON_DEPTH('{"x": {"y": 1}}');

Output:

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

Scalar values ​​and empty objects/arrays

The depth of a scalar value or an empty array/object is 1:

SELECT
    JSON_DEPTH('{}'),
    JSON_DEPTH('[]'),
    JSON_DEPTH(1);

Output:

+------------------+------------------+---------------+
| JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH(1) |
+------------------+------------------+---------------+
|                1 |                1 |             1 |
+------------------+------------------+---------------+

NULL parameters

MariaDB JSON_DEPTH will return NULL if the argument is NULL:

SELECT JSON_DEPTH(NULL);

Output:

+------------------+
| JSON_DEPTH(NULL) |
+------------------+
|             NULL |
+------------------+

Invalid JSON

If the argument is an invalid JSON, the MariaDB JSON_CONTAINS() function will return NULL, and MariaDB will generate a warning.

SELECT JSON_DEPTH('a');

Output:

+-----------------+
| JSON_DEPTH('a') |
+-----------------+
|            NULL |
+-----------------+

Let’s look at the warnings:

SHOW WARNINGS;

Output:

+---------+------+--------------------------------------------------------------------------------+
| Level   | Code | Message                                                                        |
+---------+------+--------------------------------------------------------------------------------+
| Warning | 4038 | Syntax error in JSON text in argument 1 to function 'json_depth' at position 1 |
+---------+------+--------------------------------------------------------------------------------+

Conclusion

In MariaDB, JSON_DEPTH() is a built-in function that returns the maximum depth of a given JSON document.