Purpose
You can call this function to compare 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)
Notes
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