Menu

MariaDB JSON_OVERLAPS(): Check for Common JSON Values

MariaDB JSON_OVERLAPS() compares two JSON documents and returns 1 if they share at least one common value, or 0 otherwise. It is available from MariaDB 10.9. See the official MariaDB JSON_OVERLAPS reference.

Syntax

JSON_OVERLAPS(json_doc1, json_doc2)

Examples

Arrays

Two arrays overlap when they have at least one common element:

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

1

Objects

Two objects overlap when they share at least one key-value pair. Nested values must match as complete values; a partial nested-object match is not enough:

SELECT JSON_OVERLAPS(
    '{"A": 1, "B": {"C": 2}}',
    '{"A": 2, "B": {"C": 2}}'
) AS has_common_pair;

1

Scalars

Scalar values overlap only when both their JSON types and values match. MariaDB documents the example JSON_OVERLAPS('false', 'false') as returning 1.

Choose the result you need

JSON_OVERLAPS() answers a yes-or-no question. To return the actual shared values from two arrays, use JSON_ARRAY_INTERSECT(). To compare object key-value pairs as arrays, see JSON_OBJECT_TO_ARRAY().

Advertisement