JSON_DEPTH()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Returns the maximum depth in terms of inner JSON levels in the JSON data.
USAGE
JSON_DEPTH(json_data)
Argument Name | Description |
|---|---|
| The JSON data to measure |
DETAILS
JSON_DEPTH() is a JSON function that finds the depth of valid JSON value supplied as function argument.
JSON data is valid if it conforms to ECMA-404: The JSON Data Interchange Standard.
If the function argument is a scalar value (number, string, boolean, or null), an empty JSON array, or an empty JSON object, the return value is 1.
When values are found inside a JSON array or JSON object, the return value increases by 1 for each level of nesting.
A NULL is returned if the function argument is NULL.
EXAMPLES
With Literal Values
In the following example, JSON_DEPTH() is called with JSON literal values. The result demonstrates how to measure the depth of JSON data:
SELECT JSON_DEPTH('[1, 2, 3]') AS result1,
JSON_DEPTH('{"foo": [1, 2, 3], "bar": "baz"}') AS result2,
JSON_DEPTH('[1, [2, [3]]]') AS result3;
+---------+---------+---------+
| result1 | result2 | result3 |
+---------+---------+---------+
| 2 | 3 | 4 |
+---------+---------+---------+
Example Schema and Data
Some of the examples are based on the contacts table:
CREATE TABLE contacts (
contact JSON
);
INSERT INTO contacts VALUES
('{"email": "john.smith@example.com", "name1": "John", "name2": "Smith"}'),
('{"email": ["jon.smith@example.com", "jonsmith@example.com"], "name1": "Jon", "name2": "Smith"}'),
('{"email": ["johnny.smith@example.com", {"email2": "johnnysmith@example.com"}], "name1": "Johnny", "name2": "smith"}');
Per-row Query
This example shows the depth of all the items in the above table:
SELECT contact, JSON_DEPTH(contact) AS depth
FROM contacts\G
*************************** 1. row ***************************
contact: {"email": "john.smith@example.com", "name1": "John", "name2": "Smith"}
depth: 2
*************************** 2. row ***************************
contact: {"email": ["jon.smith@example.com", "jonsmith@example.com"], "name1": "Jon", "name2": "Smith"}
depth: 3
*************************** 3. row ***************************
contact: {"email": ["johnny.smith@example.com", {"email2": "johnnysmith@example.com"}], "name1": "Johnny", "name2": "smith"}
depth: 4
JSON Depth at Specified Path
JSON_DEPTH() may be used to find JSON depth at specific path in a JSON document by combining it with a function such as JSON_EXTRACT():
SELECT contact,
JSON_DEPTH(JSON_EXTRACT(contact,'$.name1')) AS d_name1,
JSON_DEPTH(JSON_EXTRACT(contact,'$.name2')) AS d_name2,
JSON_DEPTH(JSON_EXTRACT(contact,'$.email')) AS d_email
FROM contacts \G
*************************** 1. row ***************************
contact: {"email": "john.smith@example.com", "name1": "John", "name2": "Smith"}
d_name1: 1
d_name2: 1
d_email: 1
*************************** 2. row ***************************
contact: {"email": ["jon.smith@example.com", "jonsmith@example.com"], "name1": "Jon", "name2": "Smith"}
d_name1: 1
d_name2: 1
d_email: 2
*************************** 3. row ***************************
contact: {"email": ["johnny.smith@example.com", {"email2": "johnnysmith@example.com"}], "name1": "Johnny", "name2": "smith"}
d_name1: 1
d_name2: 1
d_email: 3