MariaDB JSON_ARRAY_INTERSECT(): Find Common Array Values

MariaDB JSON_ARRAY_INTERSECT() compares two JSON arrays and returns a JSON array containing items present in both. The function is available from MariaDB 11.2. See the official MariaDB JSON_ARRAY_INTERSECT reference.

Syntax

JSON_ARRAY_INTERSECT(array1, array2)

Example

Find the values shared by two arrays:

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

+---------------+
| common_values |
+---------------+
| [1, 2]        |
+---------------+

Compare nested arrays as values

An array can also be an item in the input arrays. MariaDB compares each nested array as a complete value, so the same elements in a different order do not match:

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

+------------------------+
| common_arrays          |
+------------------------+
| [[1, 2, 3], [4, 5, 6]] |
+------------------------+

Here, [1, 2, 3] and [4, 5, 6] occur in both inputs. [1, 1, 1] has no exact match, and [1, 3, 2] is not equal to [1, 2, 3] because the element order differs. This example follows MariaDB’s JSON_ARRAY_INTERSECT release-note example.

For a Boolean check that two JSON documents overlap, see JSON_OVERLAPS(). For an array of all values from a query result, see JSON_ARRAYAGG().