Doctrine with JSON_CONTAINS search for multiple values

Question

I have JSON field in database which is something like: [1,2,3,4,5,6,7,8,9,10,11,12] I'm trying to make query, where I could find combination from two other arrays, which are like: [1,2,3,4] and [3,4,5,6] I need to find records, which have values form both arrays. Right now, I'm doing loop within loop for arrays and calling query like:

$this->createQueryBuilder('g')
            ->select('
                g.tripId as tripId,
                g.routeId as routeId,
                g.stops as stops
            ')
            ->where("JSON_CONTAINS(g.stops, :firstStopId, '$') = true AND JSON_CONTAINS(g.stops, :lastStopId, '$') = true")
            ->setParameter('firstStopId', sprintf('"%s"', $firstStopId))
            ->setParameter('lastStopId', sprintf('"%s"', $lastStopId))
            ->getQuery()
            ->getArrayResult()

Is there any way to optimise this? Maybe there is a way to search if Json field has ANY value of array?

Answer 1

Use JSON_OVERLAPS().

mysql> set @a1='[1,2,3,4,5,6,7,8,9,10,11,12]';

mysql> set @a2='[3,4,5,6]';

mysql> set @a3='[7,9,11,13]';

mysql> select json_contains(cast(@a1 as json), cast(@a2 as json)) as contained;
+-----------+
| contained |
+-----------+
|         1 |
+-----------+

mysql> select json_contains(cast(@a1 as json), cast(@a3 as json)) as contained;
+-----------+
| contained |
+-----------+
|         0 |
+-----------+

mysql> select json_overlaps(cast(@a1 as json), cast(@a3 as json)) as overlapped;
+------------+
| overlapped |
+------------+
|          1 |
+------------+

If you want to really optimize it, read about MySQL's multi-valued indexes.