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

In MariaDB, JSON_EXISTS()<\/code> is a built-in function that allows you to check whether a value exists at a specified path in the JSON document.<\/p>\n\n\n\n

It accepts the JSON document as an argument, and returns 1<\/code> if the path is found, and 0<\/code> if it’s not.<\/p>\n\n\n\n

The MariaDB documentation<\/a> states that the function “determines whether a specified JSON value exists in the given data”. However, the function doesn’t actually appear to check for a given value. It’s probably more accurate to say that it determines whether a specified path<\/em> exists, or that a value exists at the specified path.<\/p>\n\n\n\n

For checking that the actual value<\/em> exists, you can use the JSON_CONTAINS()<\/a><\/code> function.<\/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_EXISTS(json_doc, path)<\/code><\/pre>\n\n\n\n
Where json_doc<\/code> is the JSON document and path<\/code> is the path to find.<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s an example to demonstrate.<\/p>\n\n\n\n
SELECT JSON_EXISTS('{ \"name\": \"Wag\" }', '$.name');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------------------------+\n| JSON_EXISTS('{ \"name\": \"Wag\" }', '$.name') |\n+--------------------------------------------+\n|                                          1 |\n+--------------------------------------------+<\/pre>\n\n\n\n
In this case, the path is found and the result is 1<\/code>.<\/p>\n\n\n\n
If the path isn’t found, the result is 0<\/code>, like this:<\/p>\n\n\n\n
SELECT JSON_EXISTS('{ \"name\": \"Wag\" }', '$.type');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------------------------+\n| JSON_EXISTS('{ \"name\": \"Wag\" }', '$.type') |\n+--------------------------------------------+\n|                                          0 |\n+--------------------------------------------+<\/pre>\n\n\n\n
Arrays<\/h2>\n\n\n\n
In this example, I check for the existence of an element at a given index of an array:<\/p>\n\n\n\n
SELECT JSON_EXISTS(\n    '{ \n        \"name\": \"Wag\", \n        \"scores\": [ 10, 8, 7 ] \n    }', \n    \"$.scores[2]\"\n    ) AS Result;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------+\n| Result |\n+--------+\n|      1 |\n+--------+<\/pre>\n\n\n\n
Here’s what happens if the index doesn’t exist in the array:<\/p>\n\n\n\n
SELECT JSON_EXISTS(\n    '{ \n        \"name\": \"Wag\", \n        \"scores\": [ 10, 8, 7 ] \n    }', \n    '$.scores[3]'\n    ) AS Result;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------+\n| Result |\n+--------+\n|      0 |\n+--------+<\/pre>\n\n\n\n
Larger JSON Document<\/h2>\n\n\n\n
Here’s an example that uses a slightly larger JSON document. Here, I check for a key that exists within several levels of nesting:<\/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_EXISTS(\n    @json_document, \n    '$.details.awards.Sumo 2020'\n    ) AS Result;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------+\n| Result |\n+--------+\n|      1 |\n+--------+<\/pre>\n\n\n\n
Null Arguments<\/h2>\n\n\n\n
If any of the arguments are NULL<\/code>, the result is NULL<\/code>:<\/p>\n\n\n\n
SELECT \n    JSON_EXISTS(null, '$.a'),\n    JSON_EXISTS('{ \"a\": 1 }', null);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------+---------------------------------+\n| JSON_EXISTS(null, '$.a') | JSON_EXISTS('{ \"a\": 1 }', null) |\n+--------------------------+---------------------------------+\n|                     NULL |                            NULL |\n+--------------------------+---------------------------------+<\/pre>\n\n\n\n
Invalid JSON<\/h2>\n\n\n\n
Passing invalid JSON results in NULL<\/code>:<\/p>\n\n\n\n
SELECT JSON_EXISTS('{1}', '$.a');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------------------------+\n| JSON_EXISTS('{1}', '$.a') |\n+---------------------------+\n|                      NULL |\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_EXISTS();<\/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_EXISTS'<\/pre>\n\n\n\n
It’s the same when you provide too many arguments:<\/p>\n\n\n\n
SELECT JSON_EXISTS('{\"a\": 1}', '$.a', 3);<\/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_EXISTS'<\/pre>\n","protected":false},"excerpt":{"rendered":"
In MariaDB, JSON_EXISTS() is a built-in function that allows you to check whether a value exists at a specified path in the JSON document. It accepts the JSON document as an argument, and returns 1 if the path is found, and 0 if it’s not. The MariaDB documentation states that the function “determines whether a specified JSON value exists … Read more<\/a><\/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-18326","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\/18326","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=18326"}],"version-history":[{"count":7,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18326\/revisions"}],"predecessor-version":[{"id":18450,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18326\/revisions\/18450"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18326"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18326"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18326"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}