{"id":18319,"date":"2021-07-07T08:26:05","date_gmt":"2021-07-06T22:26:05","guid":{"rendered":"https:\/\/database.guide\/?p=18319"},"modified":"2021-07-07T08:26:05","modified_gmt":"2021-07-06T22:26:05","slug":"mariadb-json_depth-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-json_depth-explained\/","title":{"rendered":"MariaDB JSON_DEPTH() Explained"},"content":{"rendered":"\n

In MariaDB, JSON_DEPTH()<\/code> is a built-in function that allows you to check the depth of a JSON document.<\/p>\n\n\n\n

It accepts the JSON document as an argument, and returns the maximum depth of the document.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

JSON_DEPTH(json_doc)<\/code><\/pre>\n\n\n\n
Where json_doc<\/code> is the JSON document for which to return the depth.<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s an example to demonstrate.<\/p>\n\n\n\n
SELECT JSON_DEPTH('{ \"name\": \"Wag\" }');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------------------------------+\n| JSON_DEPTH('{ \"name\": \"Wag\" }') |\n+---------------------------------+\n|                               2 |\n+---------------------------------+<\/pre>\n\n\n\n
In this case, the depth is 2<\/code>.<\/p>\n\n\n\n
Scalar Values & Empty Objects\/Arrays<\/h2>\n\n\n\n
Scalar values or empty arrays or objects have a depth of 1<\/code>:<\/p>\n\n\n\n
SELECT \n    JSON_DEPTH('{}'),\n    JSON_DEPTH('[]'),\n    JSON_DEPTH(1);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------+------------------+---------------+\n| JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH(1) |\n+------------------+------------------+---------------+\n|                1 |                1 |             1 |\n+------------------+------------------+---------------+<\/pre>\n\n\n\n
Deeper JSON Document<\/h2>\n\n\n\n
Here’s an example that uses a JSON document with a depth of 4<\/code>:<\/p>\n\n\n\n
SET @json_document = '\n    { \n        \"_id\" : 1, \n        \"name\" : \"Wag\", \n        \"details\" : {\n            \"type\" : \"Dog\", \n            \"weight\" : 20,\n            \"awards\" : { \n                \"Florida Dog Awards\" : \"Top Dog\", \n                \"New York Marathon\" : \"Fastest Dog\", \n                \"Sumo 2020\" : \"Biggest Dog\"\n            }\n        }\n    }\n';\nSELECT JSON_DEPTH(@json_document);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+----------------------------+\n| JSON_DEPTH(@json_document) |\n+----------------------------+\n|                          4 |\n+----------------------------+<\/pre>\n\n\n\n
Null Arguments<\/h2>\n\n\n\n
If the argument is NULL<\/code>, the result is NULL<\/code>:<\/p>\n\n\n\n
SELECT JSON_DEPTH(null);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------+\n| JSON_DEPTH(null) |\n+------------------+\n|             NULL |\n+------------------+<\/pre>\n\n\n\n
Invalid JSON<\/h2>\n\n\n\n
Passing invalid JSON results in NULL<\/code> with a warning:<\/p>\n\n\n\n
SELECT JSON_DEPTH('{1}');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------------------+\n| JSON_DEPTH('{1}') |\n+-------------------+\n|              NULL |\n+-------------------+\n1 row in set, 1 warning (0.000 sec)<\/pre>\n\n\n\n
Let’s see the warning:<\/p>\n\n\n\n
SHOW WARNINGS;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------+------+--------------------------------------------------------------------------------+\n| Level   | Code | Message                                                                        |\n+---------+------+--------------------------------------------------------------------------------+\n| Warning | 4038 | Syntax error in JSON text in argument 1 to function 'json_depth' at position 2 |\n+---------+------+--------------------------------------------------------------------------------+<\/pre>\n\n\n\n
Incorrect Parameter Count<\/h2>\n\n\n\n
Providing no arguments results in an error:<\/p>\n\n\n\n
SELECT JSON_DEPTH();<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_DEPTH'<\/pre>\n\n\n\n
It’s the same when you provide too many arguments:<\/p>\n\n\n\n
SELECT JSON_DEPTH('{\"a\": 1}', 2);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_DEPTH'<\/pre>\n","protected":false},"excerpt":{"rendered":"
In MariaDB, JSON_DEPTH() is a built-in function that allows you to check the depth of a JSON document. It accepts the JSON document as an argument, and returns the maximum depth of the document.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[117],"tags":[93,80,20],"class_list":["post-18319","post","type-post","status-publish","format-standard","hentry","category-mariadb","tag-functions","tag-json","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18319","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=18319"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18319\/revisions"}],"predecessor-version":[{"id":18325,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18319\/revisions\/18325"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18319"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18319"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18319"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}