Select if array contains an element of another array-CodePudding

I have a table which has a JSON type field where I save a number array like [1, 2, 3, 4].

I want to select records in which its array set contains at least one element of another array I have in a php script.

I know that the JSON_CONTAINS function can be used to see if my array contains an element, but how can I select if both arrays has at least a common number (no matter in what index).

For example:

[1, 2, 3] and [5, 0, 2] -> True
[9, 2, 1] and [0, 5, 3] -> False
[4, 0, 2] and [4, 2, 6] -> True

Currently, Im using multiple JSON_CONTAINS to check if there are common elements, this way:

SELECT *
FROM mytable
WHERE JSON_CONTAINS(ar, 0, '$') OR 
      JSON_CONTAINS(ar, 1, '$') OR 
      JSON_CONTAINS(ar, 2, '$')

But I guess there may be a more elegant way of doing this.

I searched but couldn't find the appropiate function, but if this is a dupe, let me know. Thanks in advance!

CodePudding user response：

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-overlaps

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