Purpose
This function compares two JSON documents. It returns 1 if the two documents share one or more key-value pairs or array elements, and 0 otherwise.
Syntax
JSON_OVERLAPS(json_doc1, json_doc2)
Considerations
The json_doc1 and json_doc2 parameters specify the two JSON documents to compare. If both parameters are scalars, the function performs a simple equality test.
This function corresponds to JSON_CONTAINS(), which requires that all elements of the search array exist in the searched array. Therefore, JSON_CONTAINS() performs an AND operation on the search key, while JSON_OVERLAPS() performs an OR operation.
When comparing two arrays, JSON_OVERLAPS() returns 1 if they share one or more array elements, and 0 otherwise.
Queries on JSON columns of InnoDB tables in the WHERE clause can be optimized using multi-value indexes.
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 perform data type conversion. */
obclient> SELECT JSON_OVERLAPS('[4,"5",6,7]', '5');
+-----------------------------------+
| JSON_OVERLAPS('[4,"5",6,7]', '5') |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set