Laravel中如何用MySQL分组查询获取按user_id嵌套的数组（无需foreach）

嘿，这个需求完全可以通过MySQL的JSON聚合函数结合GROUP BY来实现，不用在应用层写foreach循环处理数据。我给你详细拆解一下：

首先，我们需要利用MySQL的JSON_ARRAYAGG和JSON_OBJECTAGG这两个聚合函数（注意：这两个函数要求MySQL版本在5.7.22及以上），它们能帮我们直接在SQL层面完成数据的嵌套结构聚合。

分步实现代码 #

1. 先按用户分组，聚合每条记录的数组

如果想要先得到每个用户对应的记录数组，可以用这个查询：

SELECT
    t.user_id,
    JSON_ARRAYAGG(
        JSON_ARRAY(u.name, t.section, t.message)
    ) AS user_records
FROM tracker t
JOIN user u ON t.user_id = u.user_id
GROUP BY t.user_id;

这个查询会返回每行对应一个用户，user_records字段是一个JSON数组，里面的每个元素都是该用户一条记录的[name, section, message]数组。

2. 生成以user_id为键的完整嵌套对象

如果想要直接得到一个整体的JSON对象，把所有user_id作为键、对应的记录数组作为值，可以在外层再套一层JSON_OBJECTAGG：

SELECT
    JSON_OBJECTAGG(
        grouped.user_id,
        grouped.user_records
    ) AS final_nested_data
FROM (
    SELECT
        t.user_id,
        JSON_ARRAYAGG(
            JSON_ARRAY(u.name, t.section, t.message)
        ) AS user_records
    FROM tracker t
    JOIN user u ON t.user_id = u.user_id
    GROUP BY t.user_id
) AS grouped;

执行这个查询后，final_nested_data字段就是你想要的结构：一个JSON对象，键是各个user_id，值是对应用户的[name, section, message]数组集合。

关键函数说明 #

• JSON_ARRAY(u.name, t.section, t.message)：把单条记录的三个字段打包成一个JSON数组。
• JSON_ARRAYAGG(...)：将同一个user_id下的所有单条记录数组，聚合为一个大的JSON数组。
• JSON_OBJECTAGG(user_id, user_records)：将每个user_id和对应的记录数组，聚合为一个键值对形式的JSON对象。

这样就能完全在MySQL层面完成数据结构的转换，不需要在应用层做foreach循环处理啦！

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