MySQL JSON_DEPTH() Function

Summary: in this tutorial, you will learn how to use the JSON_DEPTH() function to get the maximum depth of a JSON document.

Introduction to MySQL JSON_DEPTH() function

The JSON_DEPTH() function returns the depth of a JSON document. It calculates the maximum depth of nested objects or arrays in the JSON data.

Here’s the syntax of the JSON_DEPTH() function:

JSON_DEPTH(json_doc)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_doc is the JSON document of which you want to get the depth.

The function returns an integer that represents the maximum depth of the JSON document.

It returns NULL if the json_doc is NULL. If the json_doc is not a valid JSON document, the function will raise an error.

The JSON_DEPTH() function follows specific rules to calculate the depth of a JSON document:

• An empty array, empty object, or scalar value has depth 1.
• A nonempty array containing only elements of depth 1 or a nonempty object containing only member values of depth 1 has depth 2.
• Otherwise, a JSON document has a depth greater than 2.

In practice, you use the JSON_DEPTH() function to analyze the structure of complex JSON documents.

MySQL JSON_DEPTH() function examples

Let’s explore some practical examples of using the JSON_DEPTH() function in MySQL to illustrate the depth calculation rules.

1) Empty JSON object

The following example uses the JSON_DEPTH() function to get the maximum depth of an empty JSON object:

SELECT 
  JSON_DEPTH('{}') AS depth;Code language: SQL (Structured Query Language) (sql)

It returns 1:

+-------+
| depth |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Nonempty JSON array with depth 1 elements

The following example uses the JSON_DEPTH() function to get the maximum depth of an array that contains only scalar values:

SELECT 
  JSON_DEPTH('[1, "apple", true]') AS depth;Code language: SQL (Structured Query Language) (sql)

Output:

+-------+
| depth |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) Complex JSON Object

The following example uses the JSON_DEPTH() function to get the maximum depth of an object containing nested objects:

SELECT 
  JSON_DEPTH(
    '{"name": "John", "address": {"street": "123 Main St", "city": "New York"}, "hobbies": ["reading", "swimming"]}'
  ) AS depth;Code language: SQL (Structured Query Language) (sql)

Output:

+-------+
| depth |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the JSON_DEPTH() function to return the maximum depth of a JSON document.