为何仅单个URL被追加？Setting表JSON字段批量添加URL异常

尝试向Setting表的现有记录中添加5个新URL，执行的SQL代码如下：

DECLARE @Setting TABLE(Id INT, JsonInfo VARCHAR(Max))

INSERT INTO @Setting  -- (2 rows affected)
VALUES
(1, '{"urls":["/test1"]}'),
(2, '{"urls":["/test2"]}');

--select * from @Setting;  -- (2 rows affected)

DECLARE @newUrls TABLE(Url VARCHAR(100))

INSERT INTO @newUrls  --(5 rows affected)
VALUES
('/test3'),
('/test4'),
('/test5'),
('/test6'),
('/test7');

SELECT u.*, s.*    -- (10 rows affected)
FROM @Setting s, @newUrls u;


UPDATE @Setting -- (2 rows affected).
SET JsonInfo = REPLACE(JSON_MODIFY(s.JsonInfo, 'append $.urls', Url), '\/', '/')
FROM  @newUrls, @Setting s;


SELECT JSON_QUERY(JsonInfo, '$.urls') from @Setting;  --(2 rows affected). only '/test3' got appended

执行后发现仅/test3被成功追加，而预期是5个新URL全部添加，请问问题出在哪里？

问题原因与解决方案 #

问题根源 #

你的UPDATE语句用逗号连接@newUrls和@Setting，属于交叉连接写法，会生成10条匹配记录（2条Setting × 5条newUrls）。但SQL Server处理UPDATE时，对每个Setting记录只会执行一次更新操作，默认取交叉连接结果集中的第一行数据（也就是/test3），剩下的4条匹配行不会生效，所以最终每个Setting记录只追加了第一个新URL。

解决方案 #

要一次性把所有新URL追加到原有数组里，需先将@newUrls中的所有URL合并成一个JSON数组，再通过JSON_MODIFY一次性追加到每个Setting记录的urls数组中，具体代码如下：

DECLARE @Setting TABLE(Id INT, JsonInfo VARCHAR(Max))

INSERT INTO @Setting
VALUES
(1, '{"urls":["/test1"]}'),
(2, '{"urls":["/test2"]}');

DECLARE @newUrls TABLE(Url VARCHAR(100))

INSERT INTO @newUrls
VALUES
('/test3'),
('/test4'),
('/test5'),
('/test6'),
('/test7');

-- 将所有新URL合并为一个JSON数组字符串
DECLARE @newJsonArray NVARCHAR(MAX) = (
    SELECT QUOTENAME(Url, '"') AS [value]
    FROM @newUrls
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);

-- 一次性追加整个数组到原有urls中
UPDATE @Setting
SET JsonInfo = REPLACE(
    JSON_MODIFY(JsonInfo, 'append $.urls', JSON_QUERY('[' + @newJsonArray + ']')),
    '\/', '/'
);

-- 验证结果
SELECT JSON_QUERY(JsonInfo, '$.urls') FROM @Setting;

代码说明 #

1. 生成JSON数组：通过FOR JSON PATH把@newUrls中的所有URL转换成JSON数组格式的字符串，WITHOUT_ARRAY_WRAPPER避免自动添加外层数组，方便后续拼接。
2. 批量追加数组：使用JSON_MODIFY的append操作，结合JSON_QUERY把整个新数组追加到原有urls数组中，确保每个Setting记录一次性获得所有5个新URL。
3. 转义处理：REPLACE函数用来去除JSON_MODIFY自动添加的转义斜杠，还原正确的URL格式。

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