{"id":18374,"date":"2021-07-09T09:11:52","date_gmt":"2021-07-08T23:11:52","guid":{"rendered":"https:\/\/database.guide\/?p=18374"},"modified":"2021-07-09T09:11:52","modified_gmt":"2021-07-08T23:11:52","slug":"mariadb-json_length-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-json_length-explained\/","title":{"rendered":"MariaDB JSON_LENGTH() Explained"},"content":{"rendered":"\n<p>In MariaDB, <code>JSON_LENGTH()<\/code> is a built-in function that returns the length of a JSON document.<\/p>\n\n\n\n<p>When you call this function, you provide the JSON document as an argument. You can also provide a path argument to return the length of a value within the document.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p>The length is determined as follows:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>A scalar&#8217;s length is always 1.<\/li><li>If an array, the number of elements in the array.<\/li><li>If an object, the number of members in the object.<\/li><\/ul>\n\n\n\n<p>The length of nested arrays or objects are not counted.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Syntax<\/h2>\n\n\n\n<p>The syntax goes like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>JSON_LENGTH(json_doc&#91;, path])<\/code><\/pre>\n\n\n\n<p>Where <code>json_doc<\/code> is the JSON document, and <code>path<\/code> is an optional argument that specifies a path within the document. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Example<\/h2>\n\n\n\n<p>Here&#8217;s an example to demonstrate.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @json_document = '\n    { \n        \"name\": \"Wag\", \n        \"type\": \"Dog\", \n        \"weight\": 20 \n    }\n';\nSELECT JSON_LENGTH(@json_document);<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+-----------------------------+\n| JSON_LENGTH(@json_document) |\n+-----------------------------+\n|                           3 |\n+-----------------------------+<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Specifying a Path<\/h2>\n\n\n\n<p>Here&#8217;s an example of specifying a path within the document:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @json_document = '\n    { \n        \"_id\" : 1, \n        \"name\" : \"Wag\", \n        \"details\" : {\n            \"type\" : \"Dog\",\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_LENGTH(@json_document, '$.details');<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+------------------------------------------+\n| JSON_LENGTH(@json_document, '$.details') |\n+------------------------------------------+\n|                                        2 |\n+------------------------------------------+<\/pre>\n\n\n\n<p>As mentioned, it doesn&#8217;t count the length of nested objects, so we get a result of <code>2<\/code>.<\/p>\n\n\n\n<p>The next example goes down a level and counts the length of the <code>awards<\/code> object:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @json_document = '\n    { \n        \"_id\" : 1, \n        \"name\" : \"Wag\", \n        \"details\" : {\n            \"type\" : \"Dog\",\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_LENGTH(@json_document, '$.details.awards');<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+-------------------------------------------------+\n| JSON_LENGTH(@json_document, '$.details.awards') |\n+-------------------------------------------------+\n|                                               3 |\n+-------------------------------------------------+<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Scalars<\/h2>\n\n\n\n<p>A scalar&#8217;s length is always <code>1<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT JSON_LENGTH( \n    '{ \"a\" : 378, \"b\" : 45 }', \n    '$.a' \n    ) AS Result;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+--------+\n| Result |\n+--------+\n|      1 |\n+--------+<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Arrays<\/h2>\n\n\n\n<p>If the document is an array, <code>JSON_LENGTH()<\/code> counts the number of elements in the array:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @json_document = '\n    { \n        \"name\": \"Wag\", \n        \"type\": \"Dog\", \n        \"scores\": &#91;9, 7, 8, 10, 3] \n    }\n';\nSELECT JSON_LENGTH(@json_document, '$.scores');<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+-----------------------------------------+\n| JSON_LENGTH(@json_document, '$.scores') |\n+-----------------------------------------+\n|                                       5 |\n+-----------------------------------------+<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Non-Existent Paths<\/h2>\n\n\n\n<p>Passing a path that doesn&#8217;t exist in the JSON document results in <code>NULL<\/code>.<\/p>\n\n\n\n<p>Example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET @json_document = '\n    { \n        \"name\": \"Wag\", \n        \"type\": \"Dog\"\n    }\n';\nSELECT JSON_LENGTH(@json_document, '$.oops');<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+---------------------------------------+\n| JSON_LENGTH(@json_document, '$.oops') |\n+---------------------------------------+\n|                                  NULL |\n+---------------------------------------+<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Empty Objects<\/h2>\n\n\n\n<p>If the selected object is empty, the result is <code>0<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT JSON_LENGTH('{}');<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+-------------------+\n| JSON_LENGTH('{}') |\n+-------------------+\n|                 0 |\n+-------------------+<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Null Arguments<\/h2>\n\n\n\n<p>If any argument is <code>NULL<\/code>, the result is <code>NULL<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \n    JSON_LENGTH(null) AS a,\n    JSON_LENGTH(null, '$.type') AS b,\n    JSON_LENGTH('{\"a\":1}', null) AS c;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+------+------+------+\n| a    | b    | c    |\n+------+------+------+\n| NULL | NULL | NULL |\n+------+------+------+<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Incorrect Parameter Count<\/h2>\n\n\n\n<p>Providing no arguments results in an error:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT JSON_LENGTH();<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_LENGTH'<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>In MariaDB, JSON_LENGTH() is a built-in function that returns the length of a JSON document. When you call this function, you provide the JSON document as an argument. You can also provide a path argument to return the length of a value within 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-18374","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\/18374","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=18374"}],"version-history":[{"count":3,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18374\/revisions"}],"predecessor-version":[{"id":18378,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18374\/revisions\/18378"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18374"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18374"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18374"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}