MySQL JSON_OVERLAPS() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_OVERLAPS() function to compare two JSON documents.

Introduction to MySQL JSON_OVERLAPS() function

The JSON_OVERLAPS() function compares two JSON documents and returns true (1) if the two documents share any key-value pairs or array elements or false (0) otherwise.

Here’s the syntax of the JSON_OVERLAPS() function:

JSON_OVERLAPS(json_doc1, json_doc2)Code language: JavaScript (javascript)

In this syntax, json_doc1 and json_doc2 are the JSON documents you want to compare.

If both arguments are scalars, the function performs a simple equality test. If either argument is NULL, the function returns NULL.

This JSON_OVERLAPS() function complements the JSON_CONTAINS() function, which necessitates the presence of all search elements in the target array.

The JSON_CONTAINS() function acts as an AND operation on search keys, while JSON_OVERLAPS() functions as an OR operation.

MySQL JSON_OVERLAPS() function examples

Let’s take some examples of using the JSON_OVERLAPS() function.

1) Using MySQL JSON_OVERLAPS() function with arrays

The following example uses the JSON_OVERLAPS() to check if two arrays share at least one element:

SELECT 
  JSON_OVERLAPS("[1,2,3]", "[3,4,5]") result;Code language: JavaScript (javascript)

It returns true because the number 3 appears on both arrays:

+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The following example returns false because two array doesn’t have any common elements:

SELECT 
  JSON_OVERLAPS("[1,2,3]", "[4,5]") result;Code language: JavaScript (javascript)

Output:

+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)

The JSON_OVERLAPS() will not carry a type checking before comparing. For example:

SELECT 
  JSON_OVERLAPS('["1","2"]', '[2,3]') result;Code language: JavaScript (javascript)

Output:

+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The function returns false because the first array has two string elements whereas the second array has two numbers.

2) Using JSON_OVERLAPS() function with objects

The following example uses the JSON_OVERLAPS() function to check if two objects share at least one key-value pair:

SELECT 
  JSON_OVERLAPS(
    '{"name": "John"}', '{"name": "John", "age": 21}'
  ) result;Code language: JavaScript (javascript)

It returns true because the objects share the same key-value pair {"name": "John"}:

+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Summary

Use the JSON_OVERLAPS() function to compare two JSON documents and return true if the two documents share any key-value pairs or array elements or false otherwise.

Was this tutorial helpful?