Azure SQL中如何修改JSON数组内指定元素的值？

我明白你的痛点——用JSON_MODIFY修改数组里的特定元素时，不知道索引就没法直接定位目标路径。别担心，咱们可以结合OPENJSON来动态找到匹配reference值的元素位置，再完成修改。以下是具体的解决方案：

核心思路 #

OPENJSON可以把JSON数组展开成关系型数据，这样我们就能通过reference值筛选出目标元素的索引（数组索引从0开始），再用CONCAT动态拼接JSON_MODIFY需要的路径，最终完成更新。

步骤1：先验证目标元素的索引（可选但推荐） #

先执行这条语句，确认你要修改的元素在数组中的位置：

SELECT 
    [key] AS ElementIndex
FROM 
    YourTable
CROSS APPLY 
    OPENJSON(JsonColumn, '$.services')
WHERE 
    JSON_VALUE(value, '$.reference') = '312ce8e7-9913-4758-82af-10551d63920a'

这里的[key]就是数组元素的索引值，比如你的示例中第一个元素的索引是0。

步骤2：执行更新语句 #

假设你的表名为YourTable，存储JSON的列名为JsonColumn，目标reference值是312ce8e7-9913-4758-82af-10551d63920a，要把Status从Active改为Disabled，可以用以下UPDATE语句：

如果列是nvarchar(max)类型（推荐） #

UPDATE YourTable
SET JsonColumn = JSON_MODIFY(
    JsonColumn,
    -- 动态拼接目标元素的JSON路径
    CONCAT('$.services[', (
        SELECT TOP 1 [key]
        FROM OPENJSON(YourTable.JsonColumn, '$.services')
        WHERE JSON_VALUE(value, '$.reference') = '312ce8e7-9913-4758-82af-10551d63920a'
    ), '].attributes.Status'),
    'Disabled'
)
-- 只更新存在目标reference的行，避免无意义操作
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(YourTable.JsonColumn, '$.services')
    WHERE JSON_VALUE(value, '$.reference') = '312ce8e7-9913-4758-82af-10551d63920a'
)

如果列是varchar(max)类型 #

因为Azure SQL的JSON函数对nvarchar支持更完善，所以需要先转换类型再修改，最后再转回去：

UPDATE YourTable
SET JsonColumn = CAST(JSON_MODIFY(
    CAST(JsonColumn AS nvarchar(max)),
    CONCAT('$.services[', (
        SELECT TOP 1 [key]
        FROM OPENJSON(CAST(JsonColumn AS nvarchar(max)), '$.services')
        WHERE JSON_VALUE(value, '$.reference') = '312ce8e7-9913-4758-82af-10551d63920a'
    ), '].attributes.Status'),
    'Disabled'
) AS varchar(max))
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(CAST(JsonColumn AS nvarchar(max)), '$.services')
    WHERE JSON_VALUE(value, '$.reference') = '312ce8e7-9913-4758-82af-10551d63920a'
)

步骤3：验证修改结果（可选） #

执行这条语句查看修改后的JSON内容，确认是否符合预期：

SELECT 
    JSON_MODIFY(
        JsonColumn,
        CONCAT('$.services[', (
            SELECT TOP 1 [key]
            FROM OPENJSON(JsonColumn, '$.services')
            WHERE JSON_VALUE(value, '$.reference') = '312ce8e7-9913-4758-82af-10551d63920a'
        ), '].attributes.Status'),
        'Disabled'
    ) AS ModifiedJson
FROM YourTable
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(JsonColumn, '$.services')
    WHERE JSON_VALUE(value, '$.reference') = '312ce8e7-9913-4758-82af-10551d63920a'
)

注意事项 #

• 如果你的JSON数组中存在多个元素匹配同一个reference值，TOP 1只会修改第一个匹配的元素。如果需要修改所有匹配项，你需要用循环或者其他方式处理，不过通常reference应该是唯一的。
• 确保你的JSON格式是有效的，否则JSON函数可能会报错。可以用ISJSON(JsonColumn) = 1来验证。

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