在Microsoft SQL Server中清空JSON列所有层级的attachments数组

要批量清空所有sections下所有subSections的attachments数组，需要逐层拆解JSON结构，修改后再重新组合。以下是完整的SQL语句：

WITH SectionCTE AS (
    -- 展开所有sections数组，获取每个section的索引和原始内容
    SELECT 
        t.Id,
        CAST(s.[key] AS INT) AS SectionIndex,
        JSON_QUERY(s.value) AS SectionData,
        t.JsonColumn
    FROM YourTable t
    CROSS APPLY OPENJSON(t.JsonColumn, '$.sections') s
),
SubSectionCTE AS (
    -- 展开每个section下的subSections数组，将attachments置为[]
    SELECT 
        sc.Id,
        sc.SectionIndex,
        CAST(ss.[key] AS INT) AS SubSectionIndex,
        -- 无论subSection是否原有attachments，统一设置为[]
        JSON_MODIFY(
            JSON_QUERY(ss.value),
            '$.attachments',
            '[]'
        ) AS ModifiedSubSection
    FROM SectionCTE sc
    CROSS APPLY OPENJSON(sc.SectionData, '$.subSections') ss
),
ModifiedSections AS (
    -- 将修改后的subSections重新组合为数组，更新对应section
    SELECT 
        sc.Id,
        sc.SectionIndex,
        COALESCE(
            JSON_MODIFY(
                sc.SectionData,
                '$.subSections',
                JSON_QUERY('[' + STRING_AGG(STRING_ESCAPE(ms.ModifiedSubSection, 'json'), ',') + ']')
            ),
            sc.SectionData -- 无subSections的section保留原内容
        ) AS ModifiedSection
    FROM SectionCTE sc
    LEFT JOIN SubSectionCTE ms 
        ON sc.Id = ms.Id AND sc.SectionIndex = ms.SectionIndex
    GROUP BY sc.Id, sc.SectionIndex, sc.SectionData
),
FinalUpdatedJSON AS (
    -- 将修改后的sections重新组合为数组，生成最终JSON
    SELECT 
        Id,
        JSON_MODIFY(
            JsonColumn,
            '$.sections',
            JSON_QUERY('[' + STRING_AGG(STRING_ESCAPE(ms.ModifiedSection, 'json'), ',') + ']')
        ) AS UpdatedJson
    FROM ModifiedSections ms
    GROUP BY Id, JsonColumn
)
-- 执行更新
UPDATE t
SET t.JsonColumn = fj.UpdatedJson
FROM YourTable t
INNER JOIN FinalUpdatedJSON fj 
    ON t.Id = fj.Id;

关键说明： #

• 逐层定位嵌套元素：通过OPENJSON依次展开sections和subSections，确保能遍历所有层级的attachments，避免只修改第一个元素的局限。
• 强制统一修改：用JSON_MODIFY将所有subSection的attachments设置为[]，无论该字段是否原本存在。
• 避免JSON转义问题：使用JSON_QUERY和STRING_ESCAPE确保重新组合的数组格式正确，不会出现多余的引号或转义字符。
• 兼容空场景：通过LEFT JOIN和COALESCE处理没有subSections的section，保证原有JSON结构不受破坏。

测试建议： #

执行UPDATE前，先运行以下查询验证修改后的JSON是否符合预期：

SELECT 
    t.Id,
    fj.UpdatedJson
FROM YourTable t
INNER JOIN FinalUpdatedJSON fj 
    ON t.Id = fj.Id;

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