The JSON_DEPTH function in MySQL is used to determine the depth of a given JSON document or JSON array. The depth of a JSON document refers to the level of nesting within the structure. In other words, it indicates how deeply elements are nested within the JSON hierarchy.

Syntax

Here is the syntax for the JSON_DEPTH function:

JSON_DEPTH(json_document)

json_document: This is the JSON document or JSON array for which you want to calculate the depth.

The JSON_DEPTH function returns an integer representing the depth of the specified JSON document.

Example

Now, let’s look at an example to illustrate how the JSON_DEPTH function works:

Suppose you have the following JSON document:

{
  "name": "John",
  "age": 30,
  "address": {
    "city": "New York",
    "postal_code": "10001",
    "coordinates": [40.7128, -74.0060]
  },
  "skills": ["SQL", "Python", "JavaScript"]
}

You can use the JSON_DEPTH function to find the depth of this JSON document:

SELECT JSON_DEPTH('{
  "name": "John",
  "age": 30,
  "address": {
    "city": "New York",
    "postal_code": "10001",
    "coordinates": [40.7128, -74.0060]
  },
  "skills": ["SQL", "Python", "JavaScript"]
}') AS Depth;

The result of this query will be: 4.

How JSON_DEPTH Function Works

The JSON_DEPTH function operates by recursively traversing the JSON document, assigning a depth level to each nested object or array. The final result is the maximum depth encountered throughout the document’s hierarchy.

Depth of Empty Elements: Empty arrays, empty objects, and scalar values are assigned a depth of 1.

Depth of Non-Empty Arrays: If an array contains only elements of depth 1, then the array as a whole is also considered to have depth 1.

Depth of Non-Empty Objects: If an object contains only member values of depth 1, then the object as a whole also has depth 1.

Depth of Deeper Nesting: For JSON documents with nested objects or arrays, the function increments the depth level for each level of nesting.

It’s important to note that the JSON_DEPTH function is particularly useful when dealing with complex JSON structures where you need to understand the nesting depth for analysis or manipulation purposes.