The MySQL JSON_OVERLAPS function is used to determine if two JSON documents share any common key-value pairs, array elements, or scalar values. It returns a value of 1 (true) if the documents have any overlap and 0 (false) if they do not. The JSON_OVERLAPS function was introduced in MySQL 8.0.17 and complements the JSON_CONTAINS function, which requires the presence of all search elements in the target array.
Syntax
JSON_OVERLAPS(json_doc1, json_doc2)
Parameters
json_doc1: The first JSON document to be compared.
json_doc2: The second JSON document to be compared.
Return Value
true (1) if the two documents share any common key-value pairs or array elements.
false (0) if the two documents do not share any common key-value pairs or array elements.
null if either json_doc1 or json_doc2 is null.
Examples
-- Comparing two arrays
SELECT JSON_OVERLAPS('["Cat", "Dog", "Horse"]', '[ "Cat", "Fish" ]');
-- Comparing two objects
SELECT JSON_OVERLAPS(
'{"name": "Bark", "type": "Dog"}',
'{"name": "Wag", "type": "Dog"}'
);
-- Comparing a scalar to an array
SELECT JSON_OVERLAPS('7', '[7, 8, 9]');
In the first example, the two arrays share the element “Cat”, so the JSON_OVERLAPS() function returns true.
In the second example, the two objects share the key-value pair “type”: “Dog”, so the JSON_OVERLAPS() function returns true.
In the third example, the scalar 7 is an element of the array [7, 8, 9], so the JSON_OVERLAPS() function returns true.
Conclusion
The JSON_OVERLAPS function is a powerful feature for working with JSON data in MySQL. It can be used to check for overlaps between JSON documents, to identify common elements in JSON documents, and to perform other complex operations on JSON data.