Why only one URL added?

Question

I'm adding five new URLs to the existing records in Setting table.

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

Why is only one URL added? I was expecting all 5 new URLs to be added.

dbfiddle

Answer 1

Here's a potential solution:

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

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

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

DECLARE @newUrls TABLE(Url VARCHAR(100))

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


UPDATE  t
SET JsonInfo = REPLACE(JSON_MODIFY(t.JsonInfo, '$.urls', JSON_QUERY(
    (
        SELECT  '[' + STRING_AGG('"' + STRING_ESCAPE(x.value, 'json') + '"', ',') + ']'
        FROM    (
            SELECT  value
            FROM    openjson(jsoninfo, '$.urls') x
            UNION ALL
            SELECT  url
            FROM    @newUrls
            ) x
        )
        )
    ), '\/', '/')
FROM    @Setting t

Basically, you recreate the array inside a subquery and then manually construct the json array from it.

If you want specific order of items in your resulting array, you can use within group of STRING_AGG to achieve that

Note, it's about time to stop using the table1,table2 join syntax