SQL Server中如何向已有JSON列添加嵌套属性NewAttr

问题背景 #

现有目标JSON列数据示例：

{ "Attr1": "AAAAAAAAA", "Attr2": 70, "Attr3": null, "Attr4": false }

需要在Attr2之后插入NewAttr嵌套属性，规则如下：

• 当SourceTable1.Column1和SourceTable2.Column2均存在时：

  "NewAttr": { "SourceTable1.Column1": "12xx12", "SourceTable2.Column2": "0192xx" }
  

• 仅SourceTable1.Column1存在时：

  "NewAttr": { "SourceTable1.Column1": "12xx12" }
  

• 仅SourceTable2.Column2存在时：

  "NewAttr": { "SourceTable2.Column2": "0192xx" }
  

• 两者均不存在时：

  "NewAttr": {}
  

之前尝试的SELECT语句无法生成符合要求的嵌套结构：

SELECT top 10000 JSON_MODIFY(dt.DestinationColumn, '$.NewAttr', st.Column1) 
FROM dbo.Joiningtable jt 
INNER JOIN dbo.DestinationTable dt ON dt.ID = jt.TID 
INNER JOIN dbo.SourceTable st ON jt.TID = st.ID

最终需求是更新DestinationTable.DestinationColumn中的JSON数据。

解决方案 #

1. 动态构建NewAttr的JSON内容 #

通过LEFT JOIN关联所有相关表，根据字段存在情况用STRING_AGG拼接键值对，生成NewAttr的JSON字符串：

SELECT 
    dt.ID,
    CONCAT(
        '{',
        STRING_AGG(
            CASE
                WHEN st1.Column1 IS NOT NULL THEN '"SourceTable1.Column1": "12xx12"'
                WHEN st2.Column2 IS NOT NULL THEN '"SourceTable2.Column2": "0192xx"'
            END,
            ','
        ),
        '}'
    ) AS NewAttrJson
FROM dbo.DestinationTable dt
LEFT JOIN dbo.Joiningtable jt ON dt.ID = jt.TID
LEFT JOIN dbo.SourceTable1 st1 ON jt.TID = st1.ID
LEFT JOIN dbo.SourceTable2 st2 ON jt.TID = st2.ID
GROUP BY dt.ID

LEFT JOIN确保即使源表无匹配数据也能处理，STRING_AGG会自动忽略空值，最终生成符合规则的JSON对象。

2. 更新目标JSON列 #

方式一：不严格要求属性顺序

直接用JSON_MODIFY将构建好的NewAttr插入到目标JSON列中，注意用JSON_QUERY避免转义：

UPDATE dt
SET dt.DestinationColumn = JSON_MODIFY(
    dt.DestinationColumn,
    '$.NewAttr',
    JSON_QUERY(na.NewAttrJson)
)
FROM dbo.DestinationTable dt
JOIN (
    SELECT 
        dt.ID,
        CONCAT(
            '{',
            STRING_AGG(
                CASE
                    WHEN st1.Column1 IS NOT NULL THEN '"SourceTable1.Column1": "12xx12"'
                    WHEN st2.Column2 IS NOT NULL THEN '"SourceTable2.Column2": "0192xx"'
                END,
                ','
            ),
            '}'
        ) AS NewAttrJson
    FROM dbo.DestinationTable dt
    LEFT JOIN dbo.Joiningtable jt ON dt.ID = jt.TID
    LEFT JOIN dbo.SourceTable1 st1 ON jt.TID = st1.ID
    LEFT JOIN dbo.SourceTable2 st2 ON jt.TID = st2.ID
    GROUP BY dt.ID
) na ON dt.ID = na.ID

方式二：严格要求NewAttr在Attr2之后

如果需要确保NewAttr位于Attr2之后，需要重新重组JSON结构：

UPDATE dt
SET dt.DestinationColumn = 
    JSON_MODIFY(
        dt.DestinationColumn,
        '$.',
        (
            SELECT 
                Attr1,
                Attr2,
                JSON_QUERY(na.NewAttrJson) AS NewAttr,
                Attr3,
                Attr4
            FROM OPENJSON(dt.DestinationColumn)
            WITH (
                Attr1 NVARCHAR(100),
                Attr2 INT,
                Attr3 NVARCHAR(100),
                Attr4 BIT
            )
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        )
    )
FROM dbo.DestinationTable dt
JOIN (
    SELECT 
        dt.ID,
        CONCAT(
            '{',
            STRING_AGG(
                CASE
                    WHEN st1.Column1 IS NOT NULL THEN '"SourceTable1.Column1": "12xx12"'
                    WHEN st2.Column2 IS NOT NULL THEN '"SourceTable2.Column2": "0192xx"'
                END,
                ','
            ),
            '}'
        ) AS NewAttrJson
    FROM dbo.DestinationTable dt
    LEFT JOIN dbo.Joiningtable jt ON dt.ID = jt.TID
    LEFT JOIN dbo.SourceTable1 st1 ON jt.TID = st1.ID
    LEFT JOIN dbo.SourceTable2 st2 ON jt.TID = st2.ID
    GROUP BY dt.ID
) na ON dt.ID = na.ID

通过OPENJSON解析原JSON，按指定顺序重新生成JSON字符串，确保NewAttr在Attr2之后。

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