Search code examples
mysqlmysql-json

Is there a possibility to find member of in two array containing "ANY" not "ALL" mysql json

Im trying to search an array within an existing one in MySQL for contains check, with the condition that even if a single element is intersecting result is true.

Tried existing functions - JSON_CONTAINS / MEMBER OF. But they seem to check "ALL" condition.

SET @j1 = '["a","b","c"]';
SET @j2 = '["d","e","f","a"]';

SELECT <OPERATOR>(J1,J2);
+------------------------+
|                      1 |
+------------------------+

Any workarounds?

Solution 
  • mysql> select json_overlaps(@j1, @j2);
+-------------------------+
| json_overlaps(@j1, @j2) |
+-------------------------+
|                       1 |
+-------------------------+

    Requires MySQL 8.0. If you're still on MySQL 5.x, see How to emulate JSON_OVERLAPS function on MySQL 5.7?