Select if array contains an element of another array

Question

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!

Bill Karwin · Accepted Answer · 2022-01-31 21:27:54Z

3

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 |
+---------------------------------------+

answered Jan 31, 2022 at 21:27

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select if array contains an element of another array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related