如何使用JSON_MODIFY修改JSON列中数组的键（Day改DayOfWeek）

现有TempTbl表，其JsonData列存储JSON数组数据：部分行的数组元素包含Day键，另一部分包含DayOfWeek键。需将所有Day键修改为DayOfWeek，使所有行的JSON数据统一包含DayOfWeek和Time键。

表结构及测试数据 #

CREATE TABLE TempTbl 
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    JsonData nvarchar(max)
)

INSERT INTO TempTbl (JsonData)
VALUES 
   ('[{"Day":1,"Time":6},{"Day":2,"Time":11},{"Day":3,"Time":16}]'),
   ('[{"DayOfWeek":0,"Time":6},{"DayOfWeek":1,"Time":6}]')

解决方案 #

直接使用JSON_MODIFY处理数组内的键存在局限性，它更适合修改值而非键名。我们可以通过解析JSON数组→重组统一结构的JSON→更新回表的方式实现需求，具体SQL如下：

更新语句 #

UPDATE TempTbl
SET JsonData = (
    SELECT 
        ISNULL(j.DayOfWeek, j.Day) AS DayOfWeek,
        j.Time AS Time
    FROM OPENJSON(JsonData)
    WITH (
        Day int '$.Day',
        DayOfWeek int '$.DayOfWeek',
        Time int '$.Time'
    ) j
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
WHERE JSON_QUERY(JsonData, '$[*].Day') IS NOT NULL -- 仅更新包含Day键的行

代码说明 #

1. 解析JSON：通过OPENJSON将每行的JSON数组拆分为独立元素，提取Day、DayOfWeek和Time字段；
2. 统一结构：用ISNULL逻辑，优先保留已有的DayOfWeek值，若不存在则使用Day的值，确保输出统一的DayOfWeek键；
3. 重组JSON：FOR JSON PATH将处理后的行重新拼接为JSON数组，WITHOUT_ARRAY_WRAPPER避免生成额外的外层数组，保证结构与原数据一致；
4. 精准更新：WHERE条件过滤出包含Day键的行，避免对已经符合要求的行重复操作。

验证结果 #

执行以下查询查看更新后的数据：

SELECT ID, JsonData FROM TempTbl

预期输出：

ID | JsonData
---|--------------------------------------------------------------------
1  | [{"DayOfWeek":1,"Time":6},{"DayOfWeek":2,"Time":11},{"DayOfWeek":3,"Time":16}]
2  | [{"DayOfWeek":0,"Time":6},{"DayOfWeek":1,"Time":6}]

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