How the JSON_ARRAY_INTERSECT() function works in Mariadb?

The MariaDB JSON_ARRAY_INTERSECT() function is used to find the intersection of two JSON arrays, returning an array containing elements that appear in both input arrays. It is particularly useful in scenarios where you need to compare lists of JSON elements and extract common values.

Syntax

The syntax for the MariaDB JSON_ARRAY_INTERSECT() function is as follows:

JSON_ARRAY_INTERSECT(json_array1, json_array2)

This function takes two arguments:

• json_array1: The first JSON array.
• json_array2: The second JSON array.

The JSON_ARRAY_INTERSECT() function returns a new JSON array containing the common elements from both input arrays.

Examples

Example 1: Basic Intersection

This example demonstrates finding the intersection of two simple JSON arrays.

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

The output for this statement is:

[2, 3]

This indicates that the numbers 2 and 3 are common to both arrays.

Example 2: Intersection with Strings

Here, we’ll find the intersection of arrays containing string elements.

SELECT JSON_ARRAY_INTERSECT('["apple", "banana"]', '["banana", "cherry"]');

The output for this statement is:

["banana"]

This result shows that the string “banana” is present in both arrays.

Example 3: No Intersection

To demonstrate what happens when there is no intersection.

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

The output for this statement is:

NULL

The NULL indicates that there are no common elements.

Example 4: Complex Arrays

This example checks the intersection of more complex JSON arrays.

SELECT JSON_ARRAY_INTERSECT('[{"id": 1}, {"id": 2}]', '[{"id": 2}, {"id": 3}]');

The output for this statement is:

[{"id": 2}]

This confirms that the object with id 2 is present in both arrays.

Example 5: Nested Arrays

JSON_ARRAY_INTERSECT() can also handle nested arrays.

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

The output for this statement is:

[[2, 3]]

This indicates that the nested array [2, 3] is common to both parent arrays.

Related Functions

Below are a few functions related to MariaDB JSON_ARRAY_INTERSECT():

• MariaDB JSON_CONTAINS() function is used to determine if a JSON document contains specific data.
• MariaDB JSON_OVERLAPS() function is used to check if two JSON arrays or objects have any common elements.
• MariaDB JSON_MERGE_PATCH() function is used to merge two or more JSON documents, replacing values of duplicate keys in the first object.

Conclusion

The JSON_ARRAY_INTERSECT() function in MariaDB is a powerful tool for working with JSON data, allowing for the comparison and extraction of common elements between JSON arrays. By understanding and utilizing this function, developers can perform complex data manipulations and analyses within their database applications. The related functions offer additional capabilities for handling JSON data, making MariaDB a robust platform for JSON manipulation.