如何编写SQL查询生成指定嵌套JSON格式的分组数据？

根据你的表结构和目标JSON格式，以下是主流数据库对应的查询语句，替换your_table_name为实际表名即可：

PostgreSQL #

利用PostgreSQL的JSON聚合函数直接实现嵌套结构：

SELECT json_agg(
    json_build_object(
        'GroupId', GroupID,
        'Data', country_data
    )
) AS result
FROM (
    SELECT 
        GroupID,
        json_agg(
            json_build_object(
                'Country', CountryName,
                'City', json_agg(CityName)
            )
        ) AS country_data
    FROM your_table_name
    GROUP BY GroupID, CountryName
    GROUP BY GroupID
) AS grouped_data;

MySQL 8.0+ #

借助JSON_ARRAYAGG和JSON_OBJECT函数完成嵌套聚合：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'GroupId', GroupID,
        'Data', country_data
    )
) AS result
FROM (
    SELECT 
        GroupID,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'Country', CountryName,
                'City', city_array
            )
        ) AS country_data
    FROM (
        SELECT 
            GroupID,
            CountryName,
            JSON_ARRAYAGG(CityName) AS city_array
        FROM your_table_name
        GROUP BY GroupID, CountryName
    ) AS city_grouped
    GROUP BY GroupID
) AS group_grouped;

SQL Server #

通过嵌套子查询结合FOR JSON PATH生成目标结构：

SELECT 
    GroupID AS 'GroupId',
    (
        SELECT 
            CountryName AS 'Country',
            (SELECT CityName FROM your_table_name t3 WHERE t3.GroupID = t1.GroupID AND t3.CountryName = t2.CountryName FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS 'City'
        FROM your_table_name t2
        WHERE t2.GroupID = t1.GroupID
        GROUP BY CountryName
        FOR JSON PATH
    ) AS 'Data'
FROM your_table_name t1
GROUP BY GroupID
FOR JSON PATH;

说明 #

• 内层先按GroupID+CountryName聚合城市为数组，再按GroupID聚合生成每个组的Data数组，最终组合成目标JSON结构
• 确保使用的数据库版本支持对应的JSON函数（比如MySQL需要8.0及以上）

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