{"id":12172,"date":"2023-11-08T01:14:55","date_gmt":"2023-11-08T08:14:55","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12172"},"modified":"2023-11-08T01:26:17","modified_gmt":"2023-11-08T08:26:17","slug":"mysql-json_overlaps","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-json\/mysql-json_overlaps\/","title":{"rendered":"MySQL JSON_OVERLAPS() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL JSON_OVERLAPS()<\/code> function to compare two JSON documents.<\/p>\n\n\n\n

Introduction to MySQL JSON_OVERLAPS() function<\/h2>\n\n\n\n

The JSON_OVERLAPS()<\/code> function compares two JSON documents and returns true (1) if the two documents share any key-value pairs or array elements or false (0) otherwise.<\/p>\n\n\n\n

Here’s the syntax of the JSON_OVERLAPS()<\/code> function:<\/p>\n\n\n

JSON_OVERLAPS(json_doc1, json_doc2)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, json_doc1<\/code> and json_doc2<\/code> are the JSON documents you want to compare.<\/p>\n\n\n\n
If both arguments are scalars, the function performs a simple equality test. If either argument is NULL<\/code>, the function returns NULL<\/code>.<\/p>\n\n\n\n
This JSON_OVERLAPS()<\/code> function complements the JSON_CONTAINS()<\/a><\/code> function, which necessitates the presence of all search elements in the target array. <\/p>\n\n\n\n
The JSON_CONTAINS()<\/code> function acts as an AND operation on search keys, while JSON_OVERLAPS()<\/code> functions as an OR operation.<\/p>\n\n\n\n
MySQL JSON_OVERLAPS() function examples<\/h2>\n\n\n\n
Let’s take some examples of using the JSON_OVERLAPS()<\/code> function.<\/p>\n\n\n\n
1) Using MySQL JSON_OVERLAPS() function with arrays<\/h3>\n\n\n\n
The following example uses the JSON_OVERLAPS()<\/code> to check if two arrays share at least one element:<\/p>\n\n\n
SELECT \n  JSON_OVERLAPS(\"[1,2,3]\"<\/span>, \"[3,4,5]\"<\/span>) result;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
It returns true because the number 3 appears on both arrays:<\/p>\n\n\n
+--------+\n| result |\n+--------+\n|      1<\/span> |\n+--------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example returns false because two array doesn’t have any common elements:<\/p>\n\n\n
SELECT \n  JSON_OVERLAPS(\"[1,2,3]\"<\/span>, \"[4,5]\"<\/span>) result;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------+\r\n| result |\r\n+--------+\r\n|      0<\/span> |\r\n+--------+\r\n1<\/span> row in<\/span> set<\/span> (0.00 sec)\r\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The JSON_OVERLAPS() will not carry a type checking before comparing. For example:<\/p>\n\n\n
SELECT \n  JSON_OVERLAPS('[\"1\",\"2\"]'<\/span>, '[2,3]'<\/span>) result;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------+\r\n| result |\r\n+--------+\r\n|      0<\/span> |\r\n+--------+\r\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The function returns false because the first array has two string elements whereas the second array has two numbers.<\/p>\n\n\n\n
2) Using JSON_OVERLAPS() function with objects<\/h3>\n\n\n\n
The following example uses the JSON_OVERLAPS()<\/code> function to check if two objects share at least one key-value pair:<\/p>\n\n\n
SELECT \n  JSON_OVERLAPS(\n    '{\"name\": \"John\"}'<\/span>, '{\"name\": \"John\", \"age\": 21}'<\/span>\n  ) result;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
It returns true because the objects share the same key-value pair {\"name\": \"John\"}<\/code>:<\/p>\n\n\n
+--------+\n| result |\n+--------+\n|      1<\/span> |\n+--------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the JSON_OVERLAPS()<\/code> function to compare two JSON documents and return true if the two documents share any key-value pairs or array elements or false otherwise.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t