为什么使用追加的JSON_MODIFY没有按预期追加

Question

我有以下(最小的)示例

-- prepare some data
DECLARE @src TABLE (txt varchar(MAX), id bigint)
insert into @src (txt, id) values ('foo', 1)
insert into @src (txt, id) values ('bar', 1)
insert into @src (txt, id) values ('baz', 1)
insert into @src (txt, id) values ('foo', 2)
insert into @src (txt, id) values ('bar', 2)
-- prepare json 
declare @dst TABLE (json varchar(max), id bigint)
insert into @dst (json, id) values ('[]', 1)
insert into @dst (json, id) values ('[]', 2)
-- peek the expected result
select s.txt, d.id from @src s 
inner join @dst d
on d.id = s.id
-- now update
update @dst set json = JSON_MODIFY(d.json, 'append strict $', s.txt)
from @src s 
inner join @dst d
on d.id = s.id
-- let's see
select * from @dst

我希望@dst表中id1的结果是['foo', 'bar', 'baz']。我有一个非常类似的例子，它可以工作，但我不能使它再次工作。

我在这里错过了什么?

Answer 1

在您的示例中，UPDATE语句不是确定性的，因为该语句包含一个FROM子句，该子句为json列返回多个值。这在"最佳实践"中有解释。在指定FROM子句以提供更新操作的标准时要小心。

UPDATE语句的结果是未定义的，如果语句包含一个FROM子句，而该子句没有以这样的方式指定，即更新的每个列出现只有一个值可用，即如果UPDATE语句不是确定性的。

但是您可以改变您的方法并使用STRING_AGG()或FOR XML PATH(如果STRING_AGG()不可用)构建预期的JSON数组。注意，不能使用FOR JSON构建标量值的JSON数组。

UPDATE @dst
SET json = a.json
FROM @dst d
CROSS APPLY (
SELECT CONCAT('[', STRING_AGG('"' + txt + '"', ','), ']') AS json
FROM @src s
WHERE s.id = d.id
) a

结果:

json                id
----------------------
["foo","bar","baz"] 1
["foo","bar"]       2