{"id":18296,"date":"2021-07-03T14:45:41","date_gmt":"2021-07-03T04:45:41","guid":{"rendered":"https:\/\/database.guide\/?p=18296"},"modified":"2021-07-03T14:45:41","modified_gmt":"2021-07-03T04:45:41","slug":"mariadb-json_contains-explained","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-json_contains-explained\/","title":{"rendered":"MariaDB JSON_CONTAINS() Explained"},"content":{"rendered":"\n

In MariaDB, JSON_CONTAINS()<\/code> is a built-in function that allows you to find out whether a specified value is found in the given JSON document, or at a specific path within the document. <\/p>\n\n\n\n

It returns 1<\/code> if it does contain the value, 0<\/code> if it doesn’t, and NULL<\/code> if any of the arguments are NULL<\/code>.<\/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_CONTAINS(json_doc, val[, path])<\/code><\/pre>\n\n\n\n
Where json_doc<\/code> is the JSON document, val<\/code> is the value to find, and path<\/code> an optional value that specifies a path within the document.<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s an example to demonstrate.<\/p>\n\n\n\n
SET @json_document = '{ \"name\": \"Wag\", \"weight\": 10 }';\n\nSELECT JSON_CONTAINS(@json_document, '{\"name\": \"Wag\"}');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------------------------------+\n| JSON_CONTAINS(@json_document, '{\"name\": \"Wag\"}') |\n+--------------------------------------------------+\n|                                                1 |\n+--------------------------------------------------+<\/pre>\n\n\n\n
In this case, there was a match, and the result is 1<\/code>.<\/p>\n\n\n\n
In the next example, there’s no match:<\/p>\n\n\n\n
SET @json_document = '{ \"name\": \"Wag\", \"weight\": 10 }';\n\nSELECT JSON_CONTAINS(@json_document, '{\"name\": \"Woof\"}');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------------------------------------------------+\n| JSON_CONTAINS(@json_document, '{\"name\": \"Woof\"}') |\n+---------------------------------------------------+\n|                                                 0 |\n+---------------------------------------------------+<\/pre>\n\n\n\n
Note that the value is enclosed in curly braces. <\/p>\n\n\n\n
Here’s what happens when the second argument is not valid:<\/p>\n\n\n\n
SET @json_document = '{ \"name\": \"Wag\", \"weight\": 10 }';\n\nSELECT JSON_CONTAINS(@json_document, 'Wag');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------------------+\n| JSON_CONTAINS(@json_document, 'Wag') |\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 2 to function 'json_contains' at position 1 |\n+---------+------+-----------------------------------------------------------------------------------+<\/pre>\n\n\n\n
Specify a Path<\/h2>\n\n\n\n
You can optionally use a third argument to specify a path.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
SET @json_document = '{ \"name\": \"Wag\", \"weight\": 10 }';\n\nSELECT JSON_CONTAINS(@json_document, 10, '$.weight');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------------------------------------------+\n| JSON_CONTAINS(@json_document, 10, '$.weight') |\n+-----------------------------------------------+\n|                                             1 |\n+-----------------------------------------------+<\/pre>\n\n\n\n
When specifying a path, I didn’t need to use curly braces.<\/p>\n\n\n\n
Here’s one searching for a string:<\/p>\n\n\n\n
SET @json_document = '{ \"name\": \"Wag\", \"weight\": 10 }';\n\nSELECT JSON_CONTAINS(@json_document, '\"Wag\"', '$.name');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+--------------------------------------------------+\n| JSON_CONTAINS(@json_document, '\"Wag\"', '$.name') |\n+--------------------------------------------------+\n|                                                1 |\n+--------------------------------------------------+<\/pre>\n\n\n\n
Notice I used double quotes inside the single quotes. If I omit the double quotes, here’s what happens:<\/p>\n\n\n\n
SET @json_document = '{ \"name\": \"Wag\", \"weight\": 10 }';\n\nSELECT JSON_CONTAINS(@json_document, 'Wag', '$.name');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------------------------------------------+\n| JSON_CONTAINS(@json_document, 'Wag', '$.name') |\n+------------------------------------------------+\n|                                           NULL |\n+------------------------------------------------+\n1 row in set, 1 warning (0.000 sec)<\/pre>\n\n\n\n
And let’s check 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 2 to function 'json_contains' at position 1 |\n+---------+------+-----------------------------------------------------------------------------------+<\/pre>\n\n\n\n
Nested Structures<\/h2>\n\n\n\n
Here’s an example that looks for a value within a nested document:<\/p>\n\n\n\n
SET @json_document = '{ \"name\": \"Wag\", \"specs\": { \"weight\": 10, \"height\": 30 } }';\n\nSELECT JSON_CONTAINS(@json_document, 30, '$.specs.height');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-----------------------------------------------------+\n| JSON_CONTAINS(@json_document, 30, '$.specs.height') |\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
SET @json_document = '{ \"name\": \"Wag\", \"weight\": 10 }';\nSELECT \n    JSON_CONTAINS(null, 10, '$.weight') AS a,\n    JSON_CONTAINS(@json_document, null, '$.weight') AS b,\n    JSON_CONTAINS(@json_document, 10, null) AS c;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------+------+------+\n| a    | b    | c    |\n+------+------+------+\n| NULL | NULL | 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_CONTAINS();<\/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_CONTAINS'<\/pre>\n\n\n\n
It’s the same when you provide too many arguments:<\/p>\n\n\n\n
SELECT JSON_CONTAINS('{ \"a\": 1}', 1, 2, 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_CONTAINS'<\/pre>\n","protected":false},"excerpt":{"rendered":"
In MariaDB, JSON_CONTAINS() is a built-in function that allows you to find out whether a specified value is found in the given JSON document, or at a specific path within the document. It returns 1 if it does contain the value, 0 if it doesn’t, and NULL if any of the arguments are NULL.<\/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-18296","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\/18296","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=18296"}],"version-history":[{"count":6,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18296\/revisions"}],"predecessor-version":[{"id":18328,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/18296\/revisions\/18328"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=18296"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=18296"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=18296"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}