如何用SQL Server 2016的TSQL生成带动态节的指定JSON结构？

前提假设表结构 #

基于需求定义基础表结构（可根据实际表名/字段名调整）：

• Agreements（公共数据）：AgreementID, AgreementDate, Position, OrganisationName, AgreementType（用于区分动态子节类型，如AgreementType1、AgreementOfADifferentType）
• OrganisationDetails：AgreementID, BusinessDate, TypeOfOrganization
• OrganisationDetails2：AgreementID, BusinessDate2, TypeOfOrganization2
• AgreementType1：AgreementID, DateOfRegistration, Amount
• AgreementOfADifferentType：AgreementID, Date, Date2, AmountOfSales, AmountOfSales2

核心实现思路 #

用FOR JSON PATH生成包含公共属性和固定子节的基础JSON片段，结合动态SQL根据协议类型拼接对应动态子表的JSON，再通过JSON_MODIFY将动态子节合并到主JSON对象中，最后统一包裹在根节点Agreement下。

完整TSQL代码 #

DECLARE @DynamicSQL NVARCHAR(MAX) = '';
DECLARE @BaseJSON NVARCHAR(MAX);

-- 1. 生成包含公共属性和固定子节的基础JSON数组
SET @BaseJSON = (
    SELECT
        a.AgreementDate,
        a.Position,
        a.OrganisationName,
        -- 固定子节OrganisationDetails
        (SELECT od.BusinessDate, od.TypeOfOrganization FROM OrganisationDetails od WHERE od.AgreementID = a.AgreementID FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS OrganisationDetails,
        -- 固定子节OrganisationDetails2
        (SELECT od2.BusinessDate2, od2.TypeOfOrganization2 FROM OrganisationDetails2 od2 WHERE od2.AgreementID = a.AgreementID FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS OrganisationsDetails2,
        a.AgreementType,
        a.AgreementID
    FROM Agreements a
    FOR JSON PATH
);

-- 2. 构造动态SQL，将每个协议的动态子节合并到基础JSON中
SET @DynamicSQL = N'
DECLARE @FinalJSON NVARCHAR(MAX) = ''{"Agreement":'' + @BaseJSON + ''}'';

-- 遍历每个协议，根据AgreementType拼接动态子节
DECLARE @AgreementCursor CURSOR;
DECLARE @AgreementID INT, @AgreementType NVARCHAR(100), @DynamicSection NVARCHAR(MAX);

SET @AgreementCursor = CURSOR FOR
SELECT AgreementID, AgreementType FROM Agreements;

OPEN @AgreementCursor;
FETCH NEXT FROM @AgreementCursor INTO @AgreementID, @AgreementType;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 根据协议类型获取对应动态表的JSON片段
    SET @DynamicSection = CASE @AgreementType
        WHEN ''AgreementType1'' THEN (SELECT DateOfRegistration, Amount FROM AgreementType1 WHERE AgreementID = @AgreementID FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
        WHEN ''AgreementOfADifferentType'' THEN (SELECT Date, Date2, AmountOfSales, AmountOfSales2 FROM AgreementOfADifferentType WHERE AgreementID = @AgreementID FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
        -- 可添加更多协议类型分支
        ELSE ''''
    END;

    -- 将动态子节合并到对应协议的JSON对象中
    IF @DynamicSection <> ''''
    BEGIN
        SET @FinalJSON = JSON_MODIFY(
            @FinalJSON,
            ''$."Agreement"['' + CAST((SELECT INDEX_KEY FROM OPENJSON(@BaseJSON) WITH (AgreementID INT) WHERE AgreementID = @AgreementID) AS NVARCHAR) + '']."'' + @AgreementType + ''"'',
            JSON_QUERY(@DynamicSection)
        );
    END;

    FETCH NEXT FROM @AgreementCursor INTO @AgreementID, @AgreementType;
END;

CLOSE @AgreementCursor;
DEALLOCATE @AgreementCursor;

-- 输出最终JSON
SELECT @FinalJSON AS FinalJSON;
';

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL, N'@BaseJSON NVARCHAR(MAX)', @BaseJSON;

关键说明 #

1. WITHOUT_ARRAY_WRAPPER：生成单个JSON对象而非数组，匹配子节的结构要求。
2. JSON_MODIFY：SQL Server 2016支持的JSON修改函数，用于将动态子节插入到对应协议的JSON对象中。
3. OPENJSON：定位基础JSON数组中对应协议的索引位置，确保动态子节插入到正确的对象。
4. 动态分支扩展：可根据实际协议类型，在CASE语句中添加更多动态表的查询分支。

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