如何使用JSON_MODIFY按索引操作JSON数组：插入、删除元素？

嘿，我明白你现在的需求——已经搞定了JSON数组末尾追加，但想在中间插入元素或者删除指定元素对吧？其实JSON_MODIFY本身没有直接的「插入中间位置」或「删除元素」的语法，但我们可以结合SQL Server的JSON函数（比如OPENJSON、JSON_ARRAYAGG）来间接实现，下面给你分两种情况讲清楚：

在JSON数组中间插入元素 #

假设你的目标是把["qwe","123"]改成["qwe","asd","123"]（在第一个元素后插入"asd"），这里分两种场景给出方案：

方案1：SQL Server 2017+（推荐，支持JSON_ARRAYAGG） #

利用OPENJSON把原数组拆成带索引的行，插入新元素后重新聚合为数组，再用JSON_MODIFY替换原列：

-- 示例：在数组的第2个位置（0基索引1）插入元素"asd"
UPDATE MyTable
SET YourJsonColumn = JSON_MODIFY(
    YourJsonColumn,
    '$', -- 替换整个JSON数组
    (
        SELECT JSON_ARRAYAGG(value ORDER BY rn) -- 按索引重新聚合为数组
        FROM (
            -- 保留原数组中索引小于插入位置的元素
            SELECT value, CAST([key] AS INT) AS rn
            FROM OPENJSON(YourJsonColumn)
            WHERE CAST([key] AS INT) < 1

            UNION ALL
            -- 插入新元素，指定它的索引位置
            SELECT 'asd' AS value, 1 AS rn

            UNION ALL
            -- 原数组中索引大于等于插入位置的元素，索引+1（给新元素腾位置）
            SELECT value, CAST([key] AS INT) + 1 AS rn
            FROM OPENJSON(YourJsonColumn)
            WHERE CAST([key] AS INT) >= 1
        ) t
    )
)
-- 可选：添加WHERE条件定位要修改的行
WHERE ...;

这个方法的好处是通用，不管原数组有多少元素都能适配，只要调整插入位置的数值即可。

方案2：SQL Server 2016兼容版（无JSON_ARRAYAGG） #

用STRING_AGG拼接JSON字符串，再通过JSON_QUERY确保结果被识别为JSON数组：

UPDATE MyTable
SET YourJsonColumn = JSON_MODIFY(
    YourJsonColumn,
    '$',
    JSON_QUERY('[' + STRING_AGG('"' + STRING_ESCAPE(value, 'json') + '"', ',') + ']')
)
FROM (
    SELECT value, CAST([key] AS INT) AS rn
    FROM OPENJSON(YourJsonColumn)
    WHERE CAST([key] AS INT) < 1

    UNION ALL
    SELECT 'asd' AS value, 1 AS rn

    UNION ALL
    SELECT value, CAST([key] AS INT) + 1 AS rn
    FROM OPENJSON(YourJsonColumn)
    WHERE CAST([key] AS INT) >= 1
) t
GROUP BY YourJsonColumn
ORDER BY rn;

STRING_ESCAPE是为了处理元素中的特殊字符（比如引号），避免JSON格式错误。

删除JSON数组中的元素 #

假设你的目标是把["qwe","123"]改成["123"]（删除第一个元素），同样分两种场景：

按索引删除元素 #

UPDATE MyTable
SET YourJsonColumn = JSON_MODIFY(
    YourJsonColumn,
    '$',
    JSON_QUERY(
        -- 过滤掉要删除的索引（这里是0），再聚合为新数组
        (SELECT JSON_ARRAYAGG(value ORDER BY CAST([key] AS INT)) 
         FROM OPENJSON(YourJsonColumn) 
         WHERE CAST([key] AS INT) != 0)
    )
)
WHERE ...;

按元素值删除元素 #

如果不知道元素的索引，只想删除值为"qwe"的元素：

UPDATE MyTable
SET YourJsonColumn = JSON_MODIFY(
    YourJsonColumn,
    '$',
    JSON_QUERY(
        (SELECT JSON_ARRAYAGG(value ORDER BY CAST([key] AS INT)) 
         FROM OPENJSON(YourJsonColumn) 
         WHERE value != 'qwe')
    )
)
WHERE ...;

注意点 #

• 用JSON_QUERY包裹聚合结果是为了避免JSON_MODIFY把数组转义成字符串（如果直接放聚合结果，会被当成带引号的字符串，破坏JSON结构）。
• 如果删除后数组为空，JSON_ARRAYAGG会返回NULL，可以用ISNULL处理成[]：ISNULL(JSON_ARRAYAGG(...), '[]')。

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