Laravel whereJsonContains查询嵌套JSON子sku字段的正确写法

你的查询返回0结果是几个写法错误共同导致的：

• 嵌套JSON路径写法错误：whereJsonContains的键值对写法不支持穿透两层数组直接匹配子字段，你写的['products->sku' => $keyword]只会匹配顶级节点下直接挂products->sku属性的结构，和你实际存储的「顶级分类数组→分类下products数组→商品sku」的两层嵌套结构不匹配。
• 匹配逻辑不符：whereJsonContains是精确值匹配，你其他字段用的是like模糊搜索，如果用户输入的是sku的部分字符，精确匹配必然返回空。
• 查询逻辑被破坏：你在闭包里直接链式调用多个orWhere没有做分组，会直接绕过外层设置的active=1、日期范围、catalog过滤条件，就算匹配到结果也会出现不符合要求的数据。
• 语法错误：原代码中->where('active', 1)后多写了一个多余的})，运行时会直接报语法错误，另外闭包里use ($category)但没有用到这个变量，属于无用引入。

基于MySQL的JSON函数实现两层嵌套数组的字段搜索，同时修正逻辑分组问题，代码如下：

$date = now()->toDateString();
$keyword = $request->filled('keyword') ? trim($request->keyword) : false;

$products = Product::whereJsonContains('catalogs', intval($request->catalog))
    ->whereDate('from', '<=', $date)
    ->whereDate('to', '>', $date)
    ->where('active', 1)
    ->orderBy('category', 'ASC')
    ->orderBy('sort', 'ASC')
    ->when($keyword, function ($query) use ($keyword) {
        // 所有OR搜索条件单独分组，避免污染外层全局过滤逻辑
        $query->where(function ($subQuery) use ($keyword) {
            $subQuery->where('sku', 'like', '%' . $keyword)
                ->orWhere('factorynumber', 'like', '%' . $keyword)
                ->orWhere('name', 'like', '%' . $keyword . '%')
                // 穿透两层数组匹配sku字段，支持模糊搜索
                ->orWhereRaw(
                    "JSON_SEARCH(childs, 'one', ?, NULL, '$[*].products[*].sku') IS NOT NULL",
                    ['%' . $keyword . '%']
                );
        });
    })
    ->get();

参数说明 #

• JSON_SEARCH是MySQL原生提供的JSON搜索函数，路径$[*].products[*].sku表示从JSON根节点开始，遍历所有顶级分类项，再遍历每个分类下的所有商品项，定位到sku字段做匹配。
• 第二个参数传'one'表示找到第一个匹配结果就停止遍历，查询性能更好。
• 绑定参数如果传$keyword（不带前后的%）就是精确匹配sku值，带%就是包含式模糊匹配，可以根据自己的搜索需求调整。

注意：如果你的应用用的是PostgreSQL数据库，JSON查询语法有差异，需要把JSON_SEARCH部分替换为PG对应的JSONB查询语句即可，其他逻辑分组部分写法通用。

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