MySQL关联查询结果转换：将扁平结构转为嵌套Category对象

要把你拿到的扁平查询结果转换成嵌套的用户-分类结构，核心是对相同用户的分类数据进行分组聚合——毕竟MySQL本身返回的是行级的扁平数据，没法直接输出嵌套的JSON数组。下面给你几种实用的实现方式：

1. 应用代码手动聚合（JavaScript/Node.js 示例） #

这是最通用的方式，不管用什么后端语言，思路都是一样的：遍历扁平结果，按用户ID分组，把同一个用户的分类数据收集到数组里。

// 假设这是你从MySQL拿到的扁平结果
const flatResults = [
  { "userTime": "14:00", "id": 1, "name": "Jack", "Category.id": 11, "Category.name": "Category1", "Category.userID": 1 },
  { "userTime": "14:00", "id": 1, "name": "Jack", "Category.id": 12, "Category.name": "Category2", "Category.userID": 1 }
];

// 用reduce分组聚合
const nestedResults = Object.values(flatResults.reduce((userMap, item) => {
  const userId = item.id;
  // 如果当前用户还没在映射里，初始化用户对象
  if (!userMap[userId]) {
    userMap[userId] = {
      userTime: item.userTime,
      id: userId,
      name: item.name,
      Category: []
    };
  }
  // 把当前分类添加到用户的分类数组中
  userMap[userId].Category.push({
    id: item["Category.id"],
    name: item["Category.name"],
    userID: item["Category.userID"]
  });
  return userMap;
}, {}));

console.log(nestedResults);
// 输出就是你想要的嵌套结构：[{ "userTime": "14:00", "id": 1, "name": "Jack", "Category": [...] }]

2. Python 后端处理示例 #

如果你的后端用Python，思路完全一致，用字典来做用户映射：

flat_results = [
    {"userTime": "14:00", "id": 1, "name": "Jack", "Category.id": 11, "Category.name": "Category1", "Category.userID": 1},
    {"userTime": "14:00", "id": 1, "name": "Jack", "Category.id": 12, "Category.name": "Category2", "Category.userID": 1}
]

user_map = {}
for item in flat_results:
    user_id = item["id"]
    if user_id not in user_map:
        # 初始化用户基础信息
        user_map[user_id] = {
            "userTime": item["userTime"],
            "id": user_id,
            "name": item["name"],
            "Category": []
        }
    # 追加分类数据
    user_map[user_id]["Category"].append({
        "id": item["Category.id"],
        "name": item["Category.name"],
        "userID": item["Category.userID"]
    })

# 把字典转成数组就是最终结果
nested_results = list(user_map.values())
print(nested_results)

3. 用ORM自动生成嵌套结果（更省心） #

如果你用ORM框架（比如Node.js的Sequelize、Python的SQLAlchemy、Java的Hibernate），这些工具已经内置了关联查询的嵌套结果支持，根本不用手动写聚合代码。比如Sequelize的例子：

// 假设已经定义好了User和Category模型，并且设置了关联关系
const users = await User.findAll({
  include: [Category] // 直接指定要关联的模型，自动返回嵌套的Category数组
});

查询返回的users就是你想要的嵌套结构，ORM会帮你自动处理分组和关联逻辑。

4. 数据库层面直接生成嵌套JSON（MySQL 5.7+） #

如果不想在应用层处理，也可以用MySQL的JSON函数直接生成嵌套结果，适合简单场景：

SELECT 
  u.userTime,
  u.id,
  u.name,
  -- 把分类聚合为JSON数组，用COALESCE处理无分类的情况（返回空数组）
  COALESCE(
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'id', c.id,
        'name', c.name,
        'userID', c.userID
      )
    ),
    JSON_ARRAY()
  ) AS Category
FROM Users u
LEFT JOIN Category c ON u.id = c.userID
-- 按用户的唯一字段分组
GROUP BY u.id, u.userTime, u.name;

这个查询会直接返回包含嵌套Category数组的结果，注意JSON_ARRAYAGG是MySQL 5.7及以上版本才支持的。

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