SQL Server生成两层可变嵌套JSON的输出问题

直接用分层嵌套的FOR JSON PATH语法搭配空值省略配置，就能同时解决空数组残留、头字段重复两个问题，完全匹配你需要的输出格式。

原有方案的问题原因 #

• JSON AUTO模式完全依赖表关联关系自动生成嵌套结构，LEFT JOIN无匹配行时会默认生成空数组，没有内置规则自动省略空数组字段
• 平铺列直接用JSON PATH时，头表字段会因为关联多行产品、附加项被重复输出，无法自动归并为单个顶层对象

可用SQL代码 #

支持SQL Server 2017及以上版本，直接运行即可得到目标结果：

SELECT 
    prop.crm_company_id,
    prop.crm_contact_id,
    prop.expected_connection_date,
    (
        SELECT 
            p.product_id,
            p.quantity,
            p.cost,
            (
                SELECT 
                    b.bolton_id,
                    b.quantity,
                    b.cost
                FROM dbo.tblProduct b
                WHERE b.ProductID = p.ID
                FOR JSON PATH
            ) AS boltons
        FROM dbo.tblProduct p
        WHERE p.ProposalID = prop.ID
            AND p.ProductID = 0
        FOR JSON PATH
    ) AS products
FROM dbo.tblProposal prop
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, ABSENT_ON_NULL

关键配置说明 #

• 分层嵌套子查询：顶层只查询头表字段，products、boltons两层数组分别通过关联子查询独立生成，从结构上避免头字段重复
• ABSENT ON NULL：当产品无匹配附加项时，boltons子查询返回NULL，该配置会自动省略值为NULL的字段，不会生成空的boltons: []结构
• WITHOUT_ARRAY_WRAPPER：顶层为单条提案记录时，该配置会去掉默认包裹的数组括号，直接输出单个JSON对象

SQL Server 2016兼容写法 #

2016版本不支持ABSENT ON NULL参数，可以用NULLIF把空数组转为NULL实现同样效果，只需要替换boltons字段的查询逻辑：

NULLIF(
    (
        SELECT 
            b.bolton_id,
            b.quantity,
            b.cost
        FROM dbo.tblProduct b
        WHERE b.ProductID = p.ID
        FOR JSON PATH
    ), '[]'
) AS boltons

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