Purpose
JSON_OVERLAPS() compares two JSON documents. If two documents have any key-value pairs or array elements in common, this function returns 1. Otherwise, it returns 0.
Syntax
JSON_OVERLAPS(json_doc1, json_doc2)
Description
json_doc1 and json_doc2 specify the two JSON documents for comparison. If both arguments are scalars, this function performs a simple equality test.
This function serves as a counterpart to JSON_CONTAINS(), which requires that all elements of an array must exist in the searched array. Therefore, JSON_CONTAINS() executes the AND operation on the search key, and JSON_OVERLAPS() executes the OR operation.
When you compare two arrays, if they share one or more array elements, JSON_OVERLAPS() returns 1. Otherwise, it returns 0.
When you use JSON_OVERLAPS() in the WHERE clause to query the JSON column of the InnoDB table, you can use multi-valued indexes to optimize the query.
Examples
obclient> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,3,5]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,3,5]") |
+---------------------------------------+
| 1 |
+---------------------------------------+
1 row in set
obclient> SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,4,6]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,4,6]") |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set
obclient> SELECT JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]');
+-----------------------------------------------------+
| JSON_OVERLAPS('[[1,2],[3,4],5]', '[1,[2,3],[4,5]]') |
+-----------------------------------------------------+
| 0 |
+-----------------------------------------------------+
1 row in set
obclient> SELECT JSON_OVERLAPS('2', '2');
+-------------------------+
| JSON_OVERLAPS('2', '2') |
+-------------------------+
| 1 |
+-------------------------+
1 row in set
obclient> SELECT JSON_OVERLAPS('[4,5,6,7]', '5');
+---------------------------------+
| JSON_OVERLAPS('[4,5,6,7]', '5') |
+---------------------------------+
| 1 |
+---------------------------------+
1 row in set
/*This function does not execute data type conversion.*/
obclient> SELECT JSON_OVERLAPS('[4,"5",6,7]', '5');
+-----------------------------------+
| JSON_OVERLAPS('[4,"5",6,7]', '5') |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set