如何将SQL JOIN结果按相同键转换为嵌套字典列表？

你需要的嵌套格式转换，Python端和SQL端都能实现，以下是两种场景的最优方案：

一、Python端实现转换 #

基础字典分组法（适用于中小数据量） #

直接通过字典按订单号分组，提取公共字段并聚合item细节，代码简洁易懂：

raw_data = [
    {"service_order_number": "ABC", "vendor_id": 0, "recipient_id": 0, "item_id": 0, "part_number": "string", "part_description": "string"},
    {"service_order_number": "ABC", "vendor_id": 0, "recipient_id": 0, "item_id": 1, "part_number": "string", "part_description": "string"},
    {"service_order_number": "DEF", "vendor_id": 0, "recipient_id": 0, "item_id": 2, "part_number": "string", "part_description": "string"},
    {"service_order_number": "DEF", "vendor_id": 0, "recipient_id": 0, "item_id": 3, "part_number": "string", "part_description": "string"}
]

grouped = {}
for item in raw_data:
    order_num = item["service_order_number"]
    # 提取订单级公共字段
    order_base = {
        "service_order_number": order_num,
        "vendor_id": item["vendor_id"],
        "recipient_id": item["recipient_id"]
    }
    # 提取item单独字段
    item_detail = {
        "item_id": item["item_id"],
        "part_number": item["part_number"],
        "part_description": item["part_description"]
    }
    # 分组聚合
    if order_num not in grouped:
        grouped[order_num] = {**order_base, "items": []}
    grouped[order_num]["items"].append(item_detail)

# 转换为目标列表格式
result = list(grouped.values())

itertools.groupby优化法（适用于大数据量） #

如果数据量较大，先排序再用groupby分组，内存效率更高：

from itertools import groupby

# 先按订单号排序（groupby要求输入已排序）
sorted_data = sorted(raw_data, key=lambda x: x["service_order_number"])
result = []

for order_num, group_items in groupby(sorted_data, key=lambda x: x["service_order_number"]):
    group_list = list(group_items)
    # 取第一个元素的公共字段（同订单字段一致）
    base_info = {
        "service_order_number": order_num,
        "vendor_id": group_list[0]["vendor_id"],
        "recipient_id": group_list[0]["recipient_id"]
    }
    # 批量生成item列表
    items = [
        {
            "item_id": item["item_id"],
            "part_number": item["part_number"],
            "part_description": item["part_description"]
        }
        for item in group_list
    ]
    result.append({**base_info, "items": items})

二、SQL端直接生成嵌套格式 #

不需要先JOIN再转换，直接通过数据库的JSON聚合函数，一步获取嵌套格式结果，不同数据库写法如下：

PostgreSQL #

使用json_agg和json_build_object聚合item字段：

SELECT 
    service_order_number,
    vendor_id,
    recipient_id,
    json_agg(
        json_build_object(
            'item_id', item_id,
            'part_number', part_number,
            'part_description', part_description
        )
    ) AS items
FROM your_joined_table
GROUP BY service_order_number, vendor_id, recipient_id;

MySQL（5.7及以上） #

使用json_arrayagg和json_object实现：

SELECT 
    service_order_number,
    vendor_id,
    recipient_id,
    json_arrayagg(
        json_object(
            'item_id', item_id,
            'part_number', part_number,
            'part_description', part_description
        )
    ) AS items
FROM your_joined_table
GROUP BY service_order_number, vendor_id, recipient_id;

SQL Server #

使用FOR JSON PATH指定嵌套结构：

SELECT 
    service_order_number,
    vendor_id,
    recipient_id,
    (SELECT 
         item_id,
         part_number,
         part_description
     FROM your_joined_table AS sub
     WHERE sub.service_order_number = main.service_order_number
       AND sub.vendor_id = main.vendor_id
       AND sub.recipient_id = main.recipient_id
     FOR JSON PATH) AS items
FROM your_joined_table AS main
GROUP BY service_order_number, vendor_id, recipient_id;

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