从SQL表输出无重复层级JSON数组的最佳实践是什么？

核心逻辑 #

要避免重复的Container、Material节点，必须采用从内到外分层聚合的思路：先聚合最细粒度的Size列表，再向上聚合Material列表，最后聚合Container列表，每一层基于父级ID分组，天然保证父节点ID唯一，不需要额外去重。

数据库端直接生成（推荐，性能最优） #

MySQL 8.0+ 示例 #

把your_table_name替换为你的实际表名即可：

SELECT JSON_OBJECT(
    'container', JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', ContainerId,
            'material', material_list
        )
    )
) AS final_json
FROM (
    -- 第二层：按Container分组，聚合所属的所有Material
    SELECT 
        ContainerId,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'id', MaterialId,
                'size', size_list
            )
        ) AS material_list
    FROM (
        -- 第一层：按Container+Material分组，聚合所属的所有Size
        SELECT 
            ContainerId,
            MaterialId,
            JSON_ARRAYAGG(JSON_OBJECT('id', SizeId)) AS size_list
        FROM your_table_name
        GROUP BY ContainerId, MaterialId
    ) t1
    GROUP BY ContainerId
) t2;

PostgreSQL 示例 #

SELECT jsonb_build_object(
    'container', jsonb_agg(
        jsonb_build_object(
            'id', "ContainerId",
            'material', material_list
        )
    )
) AS final_json
FROM (
    SELECT 
        "ContainerId",
        jsonb_agg(
            jsonb_build_object(
                'id', "MaterialId",
                'size', size_list
            )
        ) AS material_list
    FROM (
        SELECT 
            "ContainerId",
            "MaterialId",
            jsonb_agg(jsonb_build_object('id', "SizeId")) AS size_list
        FROM your_table_name
        GROUP BY "ContainerId", "MaterialId"
    ) t1
    GROUP BY "ContainerId"
) t2;

应用代码端生成（适合多数据源聚合场景） #

以Python为例，逻辑和数据库分层聚合一致，用字典做天然去重：

import json
from collections import defaultdict

# 模拟从数据库查询返回的原始数据
db_rows = [
    (848, 1, 1, 1), (849, 1, 1, 2), (850, 1, 2, 1), (851, 1, 2, 2),
    (852, 1, 3, 1), (853, 1, 4, 1), (854, 2, 2, 1), (855, 2, 2, 2), (856, 2, 2, 3)
]

# 分层存储，天然去重外层ID
container_map = defaultdict(lambda: defaultdict(list))
for row in db_rows:
    cid, mid, sid = row[1], row[2], row[3]
    container_map[cid][mid].append(sid)

# 组装目标结构
output = {"container": []}
for cid, mat_map in container_map.items():
    mat_list = []
    for mid, size_ids in mat_map.items():
        mat_list.append({
            "id": mid,
            "size": [{"id": sid} for sid in size_ids]
        })
    output["container"].append({
        "id": cid,
        "material": mat_list
    })

# 输出格式化JSON
print(json.dumps(output, indent=2, ensure_ascii=False))

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