How the JSON_DEPTH() function works in Mariadb?

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

Posted on

The JSON_DEPTH() function is a JSON function that returns the maximum depth of a JSON document. The function takes a JSON document as an argument and returns an integer that represents the number of levels in the JSON document. The depth of a JSON document is defined as follows:

  • An empty JSON document has a depth of 0.
  • A JSON document that contains a single value (such as a number, a string, a boolean, or a null) has a depth of 1.
  • A JSON document that contains an array or an object has a depth of 1 plus the maximum depth of any element in the array or any value in the object.

Syntax

The syntax of the JSON_DEPTH() function is as follows:

JSON_DEPTH(json_doc)

Where json_doc is a valid JSON document.

Examples

Example 1: Getting the depth of an empty JSON document

In this example, we use the JSON_DEPTH() function to get the depth of an empty JSON document.

SELECT JSON_DEPTH('') AS result;

The output is:

+--------+
| result |
+--------+
|   NULL |
+--------+

This means that the function returns NULL because the JSON document is empty.

Example 2: Getting the depth of a JSON document that contains a single value

In this example, we use the JSON_DEPTH() function to get the depth of a JSON document that contains a single value.

SELECT JSON_DEPTH('42') AS result;

The output is:

+--------+
| result |
+--------+
|      1 |
+--------+

This means that the function returns 1 because the JSON document contains a single value (42).

Example 3: Getting the depth of a JSON document that contains an array

In this example, we use the JSON_DEPTH() function to get the depth of a JSON document that contains an array.

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

The output is:

+--------+
| result |
+--------+
|      2 |
+--------+

This means that the function returns 2 because the JSON document contains an array with a depth of 1, and the maximum depth of any element in the array is 1.

Example 4: Getting the depth of a JSON document that contains an object

In this example, we use the JSON_DEPTH() function to get the depth of a JSON document that contains an object.

SELECT JSON_DEPTH('{"name": "Alice", "age": 25}') AS result;

The output is:

+--------+
| result |
+--------+
|      2 |
+--------+

This means that the function returns 2 because the JSON document contains an object with a depth of 1, and the maximum depth of any value in the object is 1.

Example 5: Getting the depth of a JSON document that contains a nested array

In this example, we use the JSON_DEPTH() function to get the depth of a JSON document that contains a nested array.

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

The output is:

+--------+
| result |
+--------+
|      3 |
+--------+

This means that the function returns 3 because the JSON document contains an array with a depth of 1, and the maximum depth of any element in the array is 2 (the nested array).

There are some other JSON functions that are related to the JSON_DEPTH() function. Here are some of them:

  • JSON_LENGTH(): This function returns the length of a JSON document. The function takes a JSON document and an optional path as arguments and returns the number of elements in the JSON document or the JSON value at the specified path.
  • JSON_TYPE(): This function returns the type of a JSON value. The function takes a JSON value as an argument and returns a string that indicates the type of the JSON value, such as ARRAY, OBJECT, STRING, NUMBER, BOOLEAN, or NULL.
  • JSON_VALID(): This function validates a JSON document. The function takes a JSON document as an argument and returns 1 or 0 depending on whether the JSON document is valid or not.

Conclusion

The JSON_DEPTH() function is a useful JSON function that can return the maximum depth of a JSON document. The function takes a JSON document as an argument and returns an integer that represents the number of levels in the JSON document. The depth of a JSON document is defined as the maximum depth of any value in the document. There are also some other related functions that can measure the length, type, or validity of JSON documents, such as JSON_LENGTH(), JSON_TYPE(), and JSON_VALID().