MySQL JSON_OVERLAPS() Function

In MySQL, the JSON_OVERLAPS() function detects whether two JSON documents have any of the same key-value pairs or array elements.

`JSON_OVERLAPS()` Syntax

Here is the syntax of the MySQL JSON_OVERLAPS() function:

JSON_OVERLAPS(json1, json2)

Parameters

json1: Required. A JSON document.
json2: Required. Another JSON document.

Return value

The JSON_OVERLAPS() function detects whether two JSON documents have any of the same key-value pairs or array elements. If the two JSON documents have overlapping content, the JSON_OVERLAPS() function returns 1, otherwise it returns 0.

The JSON_OVERLAPS() function compares two JSON documents according to the following rules:

When comparing two arrays, returns 1 if the two arrays have at least one same element, otherwise returns 0.
When comparing two objects, returns 1 if the two objects have at least one same key-value pair, otherwise returns 0.
When comparing two scalars, return if 1 the two scalars are the same, otherwise returns 0.
When comparing a scalar with an array, return 1 if the scalar is a element of the array, otherwise returns 0.
The result of comparing a scalar with an object is 0.
The result of comparing an array with an object is 0.
JSON_OVERLAPS() do not convert date types of parameters.

This function will return NULL if the argument is NULL.

If any parameter is not a valid JSON document, MySQL will give an error. You can use JSON_VALID() to verify the JSON document.

`JSON_OVERLAPS()` Examples

Here are some examples of JSON_OVERLAPS().

Compare arrays

SELECT JSON_OVERLAPS('[1, 2, 3]', '[3, 4, 5]');

+-----------------------------------------+
| JSON_OVERLAPS('[1, 2, 3]', '[3, 4, 5]') |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

Here, since both arrays have the same element 3, JSON_OVERLAPS() returned 1. That is to say [1, 2, 3] and [3, 4, 5] have overlaps.

Let’s look at another example:

SELECT JSON_OVERLAPS('[1, 2, [3]]', '[3, 4, 5]');

+-------------------------------------------+
| JSON_OVERLAPS('[1, 2, [3]]', '[3, 4, 5]') |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+

Here, the 3 in [1, 2, [3]] and the 3 in [3, 4, 5] are different, so it returned 0.

is returned because the elements [3]in and [3, 4, 5]in are 3 different. That is to say [1, 2, [3]] and [3, 4, 5] do not have overlaps.

Compare objects

SELECT
    JSON_OVERLAPS('{"x": 1}', '{"x": 1, "y": 2}'),
    JSON_OVERLAPS('{"x": 1}', '{"y": 2}');

+-----------------------------------------------+---------------------------------------+
| JSON_OVERLAPS('{"x": 1}', '{"x": 1, "y": 2}') | JSON_OVERLAPS('{"x": 1}', '{"y": 2}') |
+-----------------------------------------------+---------------------------------------+
|                                             1 |                                     0 |
+-----------------------------------------------+---------------------------------------+

Here, since both {"x": 1} and {"x": 1, "y": 2} have the same key-value pair "x": 1, so it returned 1.

Comparing scalars and arrays

SELECT
    JSON_OVERLAPS('[1, 2, 3]', '3'),
    JSON_OVERLAPS('[1, 2, [3]]', '3');

+---------------------------------+-----------------------------------+
| JSON_OVERLAPS('[1, 2, 3]', '3') | JSON_OVERLAPS('[1, 2, [3]]', '3') |
+---------------------------------+-----------------------------------+
|                               1 |                                 0 |
+---------------------------------+-----------------------------------+

Here, since 3 is an element of [1, 2, 3], so it returned 1.

Compare scalars

SELECT JSON_OVERLAPS('1', '1'), JSON_OVERLAPS('1', '"1"');

+-------------------------+---------------------------+
| JSON_OVERLAPS('1', '1') | JSON_OVERLAPS('1', '"1"') |
+-------------------------+---------------------------+
|                       1 |                         0 |
+-------------------------+---------------------------+

Here, since 1 and "1" are different, so JSON_OVERLAPS('1', '"1"') returned 0.

JSON_OVERLAPS() Syntax