Why does JSON_MODIFY using append not append as expected

Question

I have the following (minimal) example

-- 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

I would expect the result to be ['foo', 'bar', 'baz'] for id 1 in the @dst table. I have one very similar case in which this works but I can't get it working again.

What am I missing here?

Zhorov · Accepted Answer · 2021-02-19 08:50:56Z

In your case, the UPDATE statement is not deterministic, because the statement includes a FROM clause that returns more than one value for the json column. This is explained in the "Best practices" part of the documentation: Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.

But you may change your approach and build the expected JSON array with STRING_AGG() or FOR XML PATH(if STRING_AGG() is not available). Notice, that you can't build a JSON array of scalar values using FOR 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

Result:

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

Collectives™ on Stack Overflow

Why does JSON_MODIFY using append not append as expected

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related