基于ID条件更新JSON列中指定字段的技术咨询

假设你的表名为YourTable，自增ID列是ID，JSON列是JsonData，以下是针对SQL Server的解决方案，支持两种参数场景：

实现代码 #

DECLARE @id INT = 2, @code NVARCHAR(10) = 'B', @value NVARCHAR(20) = '$2.00';

WITH UpdatedJson AS (
    SELECT
        ID,
        JSON_MODIFY(
            JsonData,
            '$.HData',
            (
                SELECT
                    CASE WHEN j.Code = @code THEN @value ELSE j.Value END AS Value,
                    j.ID1,
                    j.Code,
                    j.[Desc]
                FROM OPENJSON(JsonData, '$.HData')
                WITH (
                    ID1 NVARCHAR(10) '$.ID1',
                    Value NVARCHAR(20) '$.Value',
                    Code NVARCHAR(10) '$.Code',
                    [Desc] NVARCHAR(50) '$.Desc'
                ) AS j
                FOR JSON PATH
            )
        ) AS NewJsonData
    FROM YourTable
    WHERE (@id IS NULL OR ID = @id)
)
UPDATE YourTable
SET JsonData = u.NewJsonData
FROM YourTable t
JOIN UpdatedJson u ON t.ID = u.ID;

代码说明 #

1. 条件筛选：通过WHERE (@id IS NULL OR ID = @id)实现两种场景的切换：
   • 当@id为空时，处理所有行
   • 当@id指定具体值时，仅处理对应ID的行
2. JSON解析与修改：
   • 用OPENJSON解析HData数组，提取每个元素的字段
   • 通过CASE语句判断元素的Code是否匹配目标值，匹配则替换Value为指定参数
   • 用FOR JSON PATH重新生成修改后的HData数组
   • 用JSON_MODIFY将原JSON中的HData节点替换为修改后的数组，保留其他JSON结构（如HeaderInfo）
3. 更新执行：通过CTE临时存储修改后的JSON数据，再关联原表完成更新

场景测试 #

• 场景1（全表更新）：将@id设为NULL，执行后所有行中HData数组里Code为"B"的元素，其Value都会被更新为"$2.00"
• 场景2（单条更新）：将@id设为2，执行后仅ID=2的行中对应元素的Value被更新

注意事项 #

• 确保JSON列的类型为NVARCHAR(MAX)或SQL Server 2016+支持的JSON类型
• Desc是SQL关键字，需用方括号[Desc]转义

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