Doctrine中JSON_CONTAINS多值查询优化及数组匹配方法咨询

一、优化当前查询的方案 #

你当前通过循环调用JSON_CONTAINS逐个判断值的方式，在数据量较大时效率偏低，可根据数据库版本（以MySQL为例）选择以下优化方案：

1. 用JSON_OVERLAPS简化多数组匹配（MySQL 8.0.17+支持） #

JSON_OVERLAPS可直接检测两个JSON数组是否存在交集，能替代多次JSON_CONTAINS调用，写法更简洁且性能更优：

$this->createQueryBuilder('g')
    ->select('
        g.tripId as tripId,
        g.routeId as routeId,
        g.stops as stops
    ')
    ->where('JSON_OVERLAPS(g.stops, :array1) = true AND JSON_OVERLAPS(g.stops, :array2) = true')
    ->setParameter('array1', json_encode([1,2,3,4]))
    ->setParameter('array2', json_encode([3,4,5,6]))
    ->getQuery()
    ->getArrayResult();

2. 虚拟列+索引（长期性能优化） #

如果这类查询频率高，建议给stops字段创建虚拟列并添加索引，将JSON数组转为可索引格式：
先创建虚拟列（假设数组元素为INT类型）：

ALTER TABLE your_table ADD stops_text TEXT GENERATED ALWAYS AS (JSON_EXTRACT(stops, '$')) VIRTUAL;

再给虚拟列创建索引：

CREATE INDEX idx_stops_text ON your_table(stops_text);

后续查询基于虚拟列进行，能大幅提升性能。

3. JSON_SEARCH批量生成条件 #

若不支持JSON_OVERLAPS，可通过JSON_SEARCH批量生成OR条件，避免循环执行查询：

// 生成第一个数组的匹配条件
$array1 = [1,2,3,4];
$cond1Parts = [];
foreach ($array1 as $val) {
    $cond1Parts[] = "JSON_SEARCH(g.stops, 'one', '$val') IS NOT NULL";
}
$condition1 = implode(' OR ', $cond1Parts);

// 生成第二个数组的匹配条件
$array2 = [3,4,5,6];
$cond2Parts = [];
foreach ($array2 as $val) {
    $cond2Parts[] = "JSON_SEARCH(g.stops, 'one', '$val') IS NOT NULL";
}
$condition2 = implode(' OR ', $cond2Parts);

$this->createQueryBuilder('g')
    ->select('
        g.tripId as tripId,
        g.routeId as routeId,
        g.stops as stops
    ')
    ->where($condition1 . ' AND ' . $condition2)
    ->getQuery()
    ->getArrayResult();

二、实现JSON字段匹配数组任意值的查询 #

要实现“JSON数组与目标数组存在至少一个共同元素”的查询，可采用以下方式：

1. JSON_OVERLAPS（推荐） #

直接利用JSON_OVERLAPS判断交集，代码简洁高效：

$this->createQueryBuilder('g')
    ->select('
        g.tripId as tripId,
        g.routeId as routeId,
        g.stops as stops
    ')
    ->where('JSON_OVERLAPS(g.stops, :targetArray) = true')
    ->setParameter('targetArray', json_encode([1,3,5]))
    ->getQuery()
    ->getArrayResult();

2. JSON_SEARCH+OR条件 #

针对不支持JSON_OVERLAPS的数据库，遍历目标数组生成OR条件：

$targetArray = [1,3,5];
$conditions = [];
foreach ($targetArray as $val) {
    $conditions[] = "JSON_SEARCH(g.stops, 'one', '$val') IS NOT NULL";
}
$conditionStr = implode(' OR ', $conditions);

$this->createQueryBuilder('g')
    ->select('
        g.tripId as tripId,
        g.routeId as routeId,
        g.stops as stops
    ')
    ->where($conditionStr)
    ->getQuery()
    ->getArrayResult();

3. JSON_CONTAINS_ANY（MariaDB 10.6+支持） #

如果使用MariaDB，可直接用JSON_CONTAINS_ANY实现：

$this->createQueryBuilder('g')
    ->select('
        g.tripId as tripId,
        g.routeId as routeId,
        g.stops as stops
    ')
    ->where('JSON_CONTAINS_ANY(g.stops, :targetArray)')
    ->setParameter('targetArray', json_encode([1,3,5]))
    ->getQuery()
    ->getArrayResult();

内容的提问来源于stack exchange，提问作者JustinasT