如何在MySQL存储的JSON字符串中搜索过滤并按journalName分组

原代码的问题 #

• 直接对查询构建器$archives调用filter()，但此时未执行数据库查询（未调用->get()或->cursor()），filter()是集合方法，会导致逻辑错误，需先获取查询结果集合再过滤。
• 分组后返回的是整个archive字段的JSON字符串/数组，而非需求中的匹配文本项。
• 手动解码JSON若处理不当（如decodeArchive函数存在问题），会导致数组解析失败，进而过滤失效。

优化实现步骤 #

1. 配置模型的JSON字段自动转换 #

在Archive模型中添加$casts属性，让Laravel自动将archive字段解析为数组，省去手动解码的繁琐操作：

class Archive extends Model
{
    protected $casts = [
        'archive' => 'array', // 自动将JSON字符串转为PHP数组
        'created_at' => 'datetime',
    ];
}

2. 数据库层面过滤（推荐，性能更优） #

若你的MySQL版本为5.7及以上，可直接用JSON函数在数据库层面过滤，避免拉取所有数据到PHP内存处理：

public function search(Request $request)
{
    $search = $request->input('search');
    if (empty($search)) {
        return []; // 处理空搜索场景
    }

    // 数据库层面筛选包含搜索关键词的archive条目
    $archives = Archive::where('user_id', auth()->user()->id)
        ->whereRaw('JSON_SEARCH(archive, "one", ?) IS NOT NULL', ["%{$search}%"])
        ->get();

    // 按journalName和日期分组，并提取匹配的文本
    $groupedResults = $archives->groupBy([
        'journalName',
        fn($item) => $item->created_at->toDateString()
    ])->map(function ($group) use ($search) {
        return $group->flatMap(function ($item) use ($search) {
            // 过滤当前条目里匹配的文本
            return collect($item->archive)->filter(function ($text) use ($search) {
                return stripos($text, $search)!== false;
            });
        })->unique(); // 去重同一分组内的重复匹配文本
    });

    return $groupedResults;
}

3. 纯PHP层面过滤（兼容旧版MySQL） #

若无法使用数据库JSON函数，修正原代码逻辑，先获取集合再过滤：

public function search(Request $request)
{
    $search = $request->input('search');
    if (empty($search)) {
        return [];
    }

    // 先获取当前用户的所有archive数据集合
    $archives = Archive::where('user_id', auth()->user()->id)->get();

    // 过滤包含匹配文本的条目
    $filteredResults = $archives->filter(function ($item) use ($search) {
        return collect($item->archive)->some(function ($text) use ($search) {
            return stripos($text, $search)!== false;
        });
    });

    // 分组并提取匹配文本
    $groupedResults = $filteredResults->groupBy([
        'journalName',
        fn($item) => $item->created_at->toDateString()
    ])->map(function ($group) use ($search) {
        return $group->flatMap(function ($item) use ($search) {
            return collect($item->archive)->filter(function ($text) use ($search) {
                return stripos($text, $search)!== false;
            });
        })->unique();
    });

    return $groupedResults;
}

关键说明 #

• 使用JSON_SEARCH时，"one"表示找到第一个匹配项即可终止搜索，"%{$search}%"用于模糊匹配，若需精确匹配可去掉百分号。
• flatMap用于合并多个数组的匹配项为一维数组，unique避免同一分组内出现重复的匹配文本。
• 模型的$casts配置是核心，确保archive字段自动转换为数组，无需手动解码。

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