JSON_OVERLAPS() - Checking JSON Overlaps

Q

How to check if two JSON values have overlaps using the JSON_OVERLAPS() function?

✍: FYIcenter.com

A

JSON_OVERLAPS(json1, json2) is a MySQL built-in function that detects overlaps of two JSON values using the following rules:

  • If both arguments are objects, overlap means they have at least one key-value pair in common.
  • If both arguments are arrays, overlap means they have at least one member in common.
  • If one argument is an array and the other is a scalar, the scalar is wrapped as an array and compared as two arrays.
  • If one argument is an object and the other is a scalar, there is no overlay.
  • If one argument is an object and the other is an array, there is no overlay.

For example:

SELECT JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]");
  -- +---------------------------------------+
  -- | JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]") |
  -- +---------------------------------------+
  -- |                                     1 |
  -- +---------------------------------------+

SELECT JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}');
  -- +-----------------------------------------------------------------------+
  -- | JSON_OVERLAPS('{"a":1,"b":10,"d":10}', '{"c":1,"e":10,"f":1,"d":10}') |
  -- +-----------------------------------------------------------------------+
  -- |                                                                     1 |
  -- +-----------------------------------------------------------------------+

SELECT JSON_OVERLAPS('5', '5');
  -- +-------------------------+
  -- | JSON_OVERLAPS('5', '5') |
  -- +-------------------------+
  -- |                       1 |
  -- +-------------------------+

SELECT JSON_OVERLAPS('[4,5,6,7]', '6');
  -- +---------------------------------+
  -- | JSON_OVERLAPS('[4,5,6,7]', '6') |
  -- +---------------------------------+
  -- |                               1 |
  -- +---------------------------------+

SELECT JSON_OVERLAPS('{"a": 6}', '6');
  -- +--------------------------------+
  -- | JSON_OVERLAPS('{"a": 6}', '6') |
  -- +--------------------------------+
  -- |                              0 |
  -- +--------------------------------+

SELECT JSON_OVERLAPS('{"a": 6}', '["a",6]');
  -- +--------------------------------------+
  -- | JSON_OVERLAPS('{"a": 6}', '["a",6]') |
  -- +--------------------------------------+
  -- |                                    0 |
  -- +--------------------------------------+

Reference information of the JSON_OVERLAPS() function:

JSON_OVERLAPS(json1, json2): int
  Returns 1 if two JSON values has an overlap. 
  Otherwise it returns 0. 

Arguments, return value and availability:
  json1, json2: Required. The JSON values to be compared.
  int: Return value. The comparison result, 0 or 1.
  Available since MySQL 8.0.

 

JSON_PRETTY() - Validating JSON Value

JSON_OBJECT() - Creating JSON Object

MySQL Functions on JSON Values

⇑⇑ MySQL Function References

2023-12-15, 270🔥, 0💬