Laravel 查询 JSON 列：高效筛选包含数组中任意值的记录

本文详解如何在 laravel 8 中使用 query builder 对 json 数组列（如 `location_ids`）进行高效过滤，支持“至少匹配一个”语义，并提供 `wherejsoncontains` 循环方案与 mysql 8.0.17+ 推荐的 `json_overlaps` 原生函数两种实现方式。

在 Laravel 应用中，当关联表的 JSON 列（例如 location_ids）存储字符串化整数数组（如 ["1", "2", "5"]），而我们需要筛选出该 JSON 数组中至少包含 $locations（整型数组，如 [1, 3, 7]）中任一元素的记录时，不能直接传入数组调用 whereJsonContains —— 因为该方法默认将整个参数作为单个值进行子串/元素匹配，而非集合交集判断。

✅ 推荐方案一：使用 JSON_OVERLAPS（MySQL ≥ 8.0.17）

若数据库版本支持（推荐生产环境优先采用），JSON_OVERLAPS 是最简洁、高效且语义清晰的原生方案。它直接判断两个 JSON 文档是否存在公共元素（自动处理类型转换与嵌套数组）：

public function scopeViewable($query)
{
    $user = Auth::user();
    $locations = $user->getShopAccess()->pluck('id')->values(); // 确保索引连续，避免 toJson() 产生关联数组

    if ($user->hasPermissionTo('users.index')) {
        return $query->whereHas('shopAccess', function (Builder $q) use ($locations) {
            $q->whereRaw('JSON_OVERLAPS(location_ids, ?)', [$locations->toJson()]);
        });
    }

    return $query->whereNull('id'); // 或其他默认限制逻辑
}

⚠️ 注意事项：

• pluck('id')->values() 确保生成标准数值索引数组（如 [0 => 1, 1 => 3]），否则 toJson() 可能输出 {"0":"1","1":"3"}（对象格式），导致 JSON_OVERLAPS 匹配失败；
• location_ids 列值必须为合法 JSON 数组（如 ["1","2","5"]），且数据库字符集需为 utf8mb4；
• 此方法在 MySQL 层完成计算，性能远优于 PHP 循环 + 多次 OR，且可利用生成列 + 索引进一步优化（如添加 JSON_CONTAINS 虚拟列索引）。

✅ 方案二：兼容低版本 MySQL 的 whereJsonContains 循环

若数据库版本低于 8.0.17，可退而求其次，对 $locations 数组逐项调用 whereJsonContains 并组合为 OR 条件：

public function scopeViewable($query)
{
    $user = Auth::user();
    $locations = $user->getShopAccess()->pluck('id')->toArray();

    if ($user->hasPermissionTo('users.index')) {
        return $query->whereHas('shopAccess', function (Builder $q) use ($locations) {
            $q->where(function ($sub) use ($locations) {
                foreach ($locations as $location) {
                    // 将整数转为字符串，匹配 JSON 中的 "1"、"2" 等
                    $sub->orWhereJsonContains('location_ids', (string) $location);
                }
            });
        });
    }

    return $query->whereNull('id');
}

? 关键细节：

• whereJsonContains('location_ids', '1') 能正确匹配 ["1","2","5"]，但不能匹配 ["10","11"]（精确元素匹配，非子串）；
• 必须将整型 $location 显式转为字符串 (string) $location，否则 Laravel 可能传递整数 1，而 JSON 中存储的是字符串 "1"，导致匹配失败；
• 当 $locations 较大时（如 > 100 项），生成的 SQL OR 链过长可能影响查询计划，建议配合缓存或预聚合优化。

? 总结与选型建议

无论采用哪种方式，请务必在 shop_access 表的 location_ids 列上建立生成列（Generated Column）并添加索引以提升查询性能，例如：

ALTER TABLE shop_access 
ADD COLUMN location_ids_json JSON AS (CAST(location_ids AS JSON)) STORED,
ADD INDEX idx_location_overlap (location_ids_json);

然后在查询中改用 JSON_OVERLAPS(location_ids_json, ?) 进一步加速。