MySQL查询结果数组JSON格式化：嵌套结构实现问题

嘿，我来帮你解决这个问题！你现在用多表JOIN查出来的结果是扁平的，转成JSON自然也是平铺的结构，要得到嵌套的层级关系，其实有两种实用的思路，我给你拆解清楚：

方法一：用MySQL自带的JSON函数直接构造嵌套结构 #

MySQL从5.7版本开始支持一系列JSON操作函数，我们可以直接在SQL里把不同表的字段打包成嵌套的JSON对象，这样查询出来的结果本身就是你想要的嵌套结构，不用再在代码里折腾。

举个例子，你可以把SQL改成这样：

SELECT 
  JSON_OBJECT(
    'player', JSON_OBJECT(
      'ID', Players.ID,
      'Name', Players.Name,
      'Age', Players.Age -- 替换成Players表的实际字段
    ),
    'school', JSON_OBJECT(
      'ID', SchoolData.ID,
      'SchoolName', SchoolData.Name, -- 可以给字段加别名避免混淆
      'location', JSON_OBJECT(
        'City', LocationData.City,
        'ZipCode', LocationData.ZipCode -- 替换成LocationData的实际字段
      )
    )
  ) AS nested_data
FROM Players 
JOIN SchoolData ON Players.School = SchoolData.ID 
JOIN LocationData ON SchoolData.Location = LocationData.ID 
WHERE Players.ID = %d;

执行这个查询后，你拿到的nested_data字段就是已经嵌套好的JSON字符串，直接返回或者转成JSON对象就行，结构完全符合你的需求。

方法二：在代码层把扁平结果组装成嵌套结构 #

如果你不想改动太多SQL，也可以在拿到查询的扁平结果后，手动在代码里重新映射字段，组装成嵌套的JSON对象。

比如假设你用Python处理，大概是这样的逻辑：

# 假设你从数据库拿到的查询结果是一个包含扁平数据的列表
flat_result = cursor.fetchone() # 因为是按ID查，应该只有一条数据

# 手动组装嵌套结构
nested_obj = {
    "player": {
        "ID": flat_result["ID"],
        "Name": flat_result["Name"],
        # 把Players表的其他字段都加进来
    },
    "school": {
        "ID": flat_result["SchoolData.ID"], # 如果查询时给字段加了别名，这里用别名更清晰
        "Name": flat_result["SchoolData.Name"],
        "location": {
            "City": flat_result["City"],
            "Address": flat_result["Address"]
            # 位置表的其他字段
        }
    }
}

# 最后转成你要的JSON格式
import json
final_json = json.dumps({"data": [nested_obj]})

这里有个小提醒：如果不同表有重名字段（比如都有ID），在写SQL的时候最好给字段加别名，比如Players.ID AS player_id，这样在代码里取值的时候就不会搞混啦。

两种方法各有优劣：SQL层处理更高效，适合数据量小或者想减少代码逻辑的场景；代码层处理更灵活，当表字段很多或者需要动态调整嵌套结构时更方便，你可以根据自己的需求选～

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