SQL Server中嵌套STRING_AGG实现三级1-n关联表JSON查询

错误原因 #

你遇到的异常是因为SQL Server不允许在聚合函数（比如STRING_AGG）内部嵌套另一个聚合函数，你的代码里在外层STRING_AGG里又调用了内层的STRING_AGG来拼接街道ID，这违反了SQL的聚合函数使用规则。

最优解法：使用SQL Server原生FOR JSON功能 #

SQL Server自带FOR JSON语法，可以自动生成符合要求的JSON结构，无需手动拼接字符串，还能自动处理转义字符，避免格式错误：

SELECT
    p.Id AS id,
    p.Name AS province_name,
    (
        -- 生成当前省份下的区县列表，包含对应街道ID数组
        SELECT
            d.Id AS id,
            d.Name AS district_name,
            -- 生成当前区县下的街道ID数组
            (SELECT w.Id FROM Ward w WHERE w.DistrictId = d.Id FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS wards
        FROM District d
        WHERE d.ProvinceId = p.Id
        FOR JSON PATH
    ) AS districts
FROM Province p
WHERE p.Id = @id
-- 去掉外层数组包装，直接返回单个省份对象
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

代码说明 #

1. 内层子查询先为每个区县生成对应的街道ID数组，WITHOUT_ARRAY_WRAPPER会去掉默认的数组外层括号，直接返回[1,2,3]形式的内容。
2. 中层子查询生成省份下的所有区县对象数组。
3. 外层查询生成最终的省份对象，同样用WITHOUT_ARRAY_WRAPPER确保返回单个对象而非数组。

备选解法：分层使用STRING_AGG #

如果必须用STRING_AGG实现，可以通过CTE分层聚合，先聚合街道到区县，再聚合区县到省份：

WITH DistrictWards AS (
    -- 第一步：聚合每个区县对应的街道ID
    SELECT
        d.Id AS DistrictId,
        d.Name AS DistrictName,
        STRING_AGG(CAST(w.Id AS VARCHAR(10)), ',') AS WardIds
    FROM District d
    LEFT JOIN Ward w ON w.DistrictId = d.Id
    GROUP BY d.Id, d.Name
)
-- 第二步：聚合区县到省份，生成最终JSON
SELECT
    CONCAT(
        '{',
        '"id":', CAST(p.Id AS VARCHAR(10)), ',',
        '"province_name":"', REPLACE(p.Name, '"', '""'), '",',
        '"districts":[',
        STRING_AGG(
            CONCAT(
                '{',
                '"id":', CAST(dw.DistrictId AS VARCHAR(10)), ',',
                '"district_name":"', REPLACE(dw.DistrictName, '"', '""'), '",',
                '"wards":[', ISNULL(dw.WardIds, ''), ']',
                '}'
            ), ','
        ),
        ']',
        '}'
    ) AS ProvinceJson
FROM Province p
LEFT JOIN DistrictWards dw ON dw.DistrictId IN (SELECT Id FROM District WHERE ProvinceId = p.Id)
WHERE p.Id = @id
GROUP BY p.Id, p.Name

注意事项 #

• 用REPLACE(p.Name, '"', '""')处理名称中的双引号，避免破坏JSON格式。
• 用ISNULL(dw.WardIds, '')处理没有街道的区县，确保生成"wards":[]而非"wards":[null]。

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