How the JSON_OVERLAPS() function works in Mariadb?

The JSON_OVERLAPS() function is a built-in function in Mariadb that allows you to check whether two JSON documents have any elements in common. It can be useful when you want to compare or filter JSON data based on their overlap.

Syntax

The syntax of the JSON_OVERLAPS() function is as follows:

JSON_OVERLAPS(json_doc1, json_doc2)

The function takes two arguments, as follows:

• json_doc1: This is the first JSON document to compare. It must be a valid JSON document or a column that contains JSON documents.
• json_doc2: This is the second JSON document to compare. It must be a valid JSON document or a column that contains JSON documents.

The function returns 1 if the two JSON documents have any elements in common, or 0 otherwise.

Examples

Example 1: Checking the overlap of two JSON arrays

In this example, we use the JSON_OVERLAPS() function to check whether two JSON arrays have any elements in common.

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

The output is:

+---------+
| overlap |
+---------+
|       1 |
+---------+

As you can see, the function returns 1 because the two JSON arrays have the element 3 in common.

Example 2: Checking the overlap of two JSON objects

In this example, we use the JSON_OVERLAPS() function to check whether two JSON objects have any elements in common.

SELECT JSON_OVERLAPS(
  '{"a": "b", "c": "d"}',
  '{"c": "d", "e": "f"}'
) AS overlap;

The output is:

+---------+
| overlap |
+---------+
|       1 |
+---------+

As you can see, the function returns 1 because the two JSON objects have the key-value pair "c": "d" in common.

Example 3: Checking the overlap of two JSON documents with different types

In this example, we use the JSON_OVERLAPS() function to check whether two JSON documents with different types have any elements in common.

SELECT JSON_OVERLAPS(
  '{"a": "b", "c": "d"}',
  '["a", "b", "c", "d"]'
) AS overlap;

The output is:

+---------+
| overlap |
+---------+
|       0 |
+---------+

As you can see, the function returns 0 because the two JSON documents have different types and cannot be compared.

Related Functions

There are some other functions in Mariadb that are related to the JSON_OVERLAPS() function. Here are some of them:

• JSON_CONTAINS() function: This function checks whether a JSON document contains another JSON document as a value. It takes a JSON document, a candidate JSON document, and an optional path argument, and returns 1 if the candidate JSON document is found within the JSON document at the specified path, or 0 otherwise. For example, JSON_CONTAINS('{"a": "b", "c": ["d", "e"]}', '"e"', '$.c') returns 1.
• JSON_CONTAINS_PATH() function: This function checks whether a JSON document contains a given path. It takes a JSON document, a mode argument, and one or more path arguments, and returns 1 if the JSON document contains all or any of the paths, depending on the mode, or 0 otherwise. For example, JSON_CONTAINS_PATH('{"a": "b", "c": ["d", "e"]}', 'one', '$.a', '$.f') returns 1.
• JSON_EXTRACT() function: This function extracts a value from a JSON document at a given path. It takes a JSON document and one or more path arguments, and returns a JSON document that contains the values from the JSON document at the specified paths. For example, JSON_EXTRACT('{"a": "b", "c": ["d", "e"]}', '$.a', '$.c[1]') returns ["b", "e"].

Conclusion

The JSON_OVERLAPS() function is a useful function in Mariadb that allows you to check whether two JSON documents have any elements in common. It can handle different types of JSON documents, such as arrays, objects, or scalars. It returns 1 if the two JSON documents have any overlap, or 0 otherwise. There are also some other functions that are related to the JSON_OVERLAPS() function, such as JSON_CONTAINS(), JSON_CONTAINS_PATH(), and JSON_EXTRACT(). You can use these functions to manipulate JSON data in different ways.