How JSON_OVERLAPS() Works in MySQL

In MySQL, JSON_OVERLAPS() is a function that tests whether or not two JSON documents have any key-value pairs or array elements in common.

The function returns true (1) if the documents have any key-value pairs or array elements in common, and false (0) if they don’t.

The JSON_OVERLAPS() function was added in MySQL 8.0.17.

Syntax

The syntax goes like this:

JSON_OVERLAPS(json_doc1, json_doc2)

Where json_doc1 and json_doc2 are the two documents to test.

Examples

Here are some examples to demonstrate how JSON_OVERLAPS() works on different JSON documents.

Arrays

Let’s compare two arrays:

SELECT JSON_OVERLAPS( '[ "Cat", "Dog", "Horse" ]', '[ "Cat", "Fish" ]' );

Result:

In this case, there was an overlap and so JSON_OVERLAPS() returned 1 (which means “true”). The overlap was due to both JSON arrays containing "Cat".

Here’s what happens when there’s no overlap:

SELECT JSON_OVERLAPS( 
    '[ "Cat", "Dog", "Horse" ]', 
    '[ "Bird", "Fish" ]' 
    );

Result:

We get a zero, which means “false”.

Partial matches don’t cut it:

SELECT JSON_OVERLAPS( 
    '[ "Cat", [ "Dog", "Horse" ] ]', 
    '[ "Dog", "Fish" ]' 
    );

Result:

This returned false because, even though Dog appears in both documents, in the first document it’s within a nested array, whereas in the second document it’s a string at the top level.

In other words, the first document contains two elements; Cat and [ "Dog", "Horse" ], and the second document contains two elements; Dog and Fish. The value of [ "Dog", "Horse" ] is different to Dog.

Objects

Here’s an example that compares two JSON objects:

SELECT JSON_OVERLAPS( 
    '{ "name" : "Wag", "type" : "Dog" }', 
    '{ "name" : "Bark", "type" : "Dog" }' 
    );

Result:

When comparing objects, JSON_OVERLAPS() compares the key/value pairs. This example returns true because both JSON documents have a type field with a value of Dog.

Here’s what happens if we change one of type fields to Cat:

SELECT JSON_OVERLAPS( 
    '{ "name" : "Wag", "type" : "Dog" }', 
    '{ "name" : "Bark", "type" : "Cat" }' 
    );

Result:

It returns false.

This shows that the overlap must be in the key/value pair. It’s not enough to just have the same key name. The values attached to that key name must be the same for it to be considered an overlap.

That said, it is enough to have just one key/value pair overlap. As seen in the above example, there’s no overlap for the name field, but that didn’t change the outcome.

Here’s another example:

SELECT JSON_OVERLAPS( 
    '{ "name" : "Wag", "type" : "Dog" }', 
    '{ "name" : "Dog", "type" : "Cat" }' 
    );

Result:

In this case, a value of Dog appears in both documents, but there’s still no overlap. This is because one of them is a value for the type key, and the other is for the name key. In other words, one is type/Dog and the other is name/Dog, and so they’re not the same.

Scalars

Here’s an example that compares two scalars:

SELECT JSON_OVERLAPS( '7', '7' );

Result:

In this case it’s a simple test for equality.

If we compare a scalar with an array, JSON_OVERLAPS() tries to treat the scalar as an array element:

SELECT JSON_OVERLAPS( '7', '[ 7, 8 ]' );

Result:

In this case, the first scalar 7 was treated as though it was [7], and so it was considered an overlap. That’s because it was as though we compared two arrays, both of which contained 7 among their elements.

Type Conversions

The JSON_OVERLAPS() function does not perform any type conversions:

SELECT JSON_OVERLAPS( '7', '[ "7", 8 ]' );

Result:

This is almost the same as a previous example that returned 1. The only difference is that I added double quotes to one of the sevens. This turned it into a string, and therefore it’s now a different type. Seeing as JSON_OVERLAP() doesn’t perform type conversions, there is no overlap, and the result is 0.

Passing the Wrong Number of Arguments

If we pass the wrong number of arguments, we get an error.

Here’s an example of passing three arguments (too many):

SELECT JSON_OVERLAPS( '7', '7', '7' );

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_OVERLAPS'

And here’s an example of passing just one argument (not enough):

SELECT JSON_OVERLAPS( '7' );

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_OVERLAPS'

The same error occurs if we don’t pass any arguments:

SELECT JSON_OVERLAPS( );

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_OVERLAPS'