如何将分组记录（DataFrame/SQL表）转为嵌套结构JSON数组

Python 实现方案 #

利用Pandas的多层groupby从底层到顶层逐层聚合，构建嵌套结构：

import pandas as pd
import json

# 构造数据集
item_dict = {
    'group_desc': ['Group C', 'Group C', 'Group C', 'Group C', 'Group C', 'Group D', 'Group D'],
    'category_desc': ['Category C', 'Category C', 'Category C', 'Category C', 'Category C', 'Category D', 'Category D'],
    'subcategory_desc': ['Subcategory A', 'Subcategory A', 'Subcategory A', 'Subcategory B', 'Subcategory B', 'Subcategory C', 'Subcategory C'],
    'item_desc': ['Item H', 'Item I', 'Item J', 'Item K', 'Item L', 'Item M', 'Item N'] 
}
hierarchy_df = pd.DataFrame(item_dict)

# 定义各层级聚合函数
def build_items(group):
    return [{'item_desc': item} for item in group['item_desc'].tolist()]

def build_subcategories(group):
    subcat_groups = group.groupby('subcategory_desc').apply(build_items).reset_index(name='items')
    return subcat_groups.to_dict('records')

def build_categories(group):
    cat_groups = group.groupby('category_desc').apply(build_subcategories).reset_index(name='subcategories')
    return cat_groups.to_dict('records')

# 构建最终嵌套结构
group_level = hierarchy_df.groupby('group_desc').apply(build_categories).reset_index(name='categories')
final_json = {'group': group_level.to_dict('records')}

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

说明 #

1. 从最底层的item开始，将每个item转换为指定字典格式并聚合为数组
2. 向上聚合subcategory，将同一子分类下的items数组关联到子分类对象
3. 继续向上聚合category，关联对应的子分类数组
4. 最后聚合group，组合所有层级得到目标嵌套JSON

PostgreSQL 实现方案 #

使用PostgreSQL的JSON函数json_agg和json_build_object，通过多层子查询从底层到顶层构建嵌套结构：

假设数据存储在表item_hierarchy中，结构与给定的DataFrame一致，执行以下SQL查询：

SELECT json_build_object(
    'group', json_agg(
        json_build_object(
            'group_desc', group_desc,
            'categories', categories
        )
    )
) AS final_json
FROM (
    SELECT 
        group_desc,
        json_agg(
            json_build_object(
                'category_desc', category_desc,
                'subcategories', subcategories
            )
        ) AS categories
    FROM (
        SELECT 
            group_desc,
            category_desc,
            json_agg(
                json_build_object(
                    'subcategory_desc', subcategory_desc,
                    'items', items
                )
            ) AS subcategories
        FROM (
            SELECT 
                group_desc,
                category_desc,
                subcategory_desc,
                json_agg(
                    json_build_object('item_desc', item_desc)
                ) AS items
            FROM item_hierarchy
            GROUP BY group_desc, category_desc, subcategory_desc
        ) AS subcat_level
        GROUP BY group_desc, category_desc
    ) AS cat_level
    GROUP BY group_desc
) AS group_level;

说明 #

1. 最内层子查询按group_desc、category_desc、subcategory_desc分组，聚合生成每个子分类对应的items数组
2. 第二层子查询按group_desc、category_desc分组，聚合生成每个分类对应的subcategories数组
3. 第三层子查询按group_desc分组，聚合生成每个分组对应的categories数组
4. 最外层构建最终的JSON结构，输出符合要求的嵌套格式

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