JSON_OVERLAPS

Description

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)

Description

The parameters json_doc1 and json_doc2 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 InnoDB tables with JSON columns in the WHERE clause using JSON_OVERLAPS() can be optimized with multi-value indexes.

Examples

SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,3,5]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,3,5]") |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,4,6]");
+---------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]", "[2,4,6]") |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.001 sec)

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 (0.001 sec)

SELECT JSON_OVERLAPS('2', '2');
+-------------------------+
| JSON_OVERLAPS('2', '2') |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.001 sec)

SELECT JSON_OVERLAPS('[4,5,6,7]', '5');
+---------------------------------+
| JSON_OVERLAPS('[4,5,6,7]', '5') |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set (0.001 sec)

/* This function does not perform data type conversion */
SELECT JSON_OVERLAPS('[4,"5",6,7]', '5');
+-----------------------------------+
| JSON_OVERLAPS('[4,"5",6,7]', '5') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.001 sec)