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

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

• sql
• json
• sql-server

Share Improve this question Follow edited Feb 21 at 3:15 Daniel B asked Feb 20 at 14:32 Daniel BDaniel B 3,18122 gold badges3737 silver badges4747 bronze badges 7

• 1 You're updating a table with two rows, how many rows do you think should be affected. You probably want to update values from all urls, but MODIFY only does it once – siggemannen Commented Feb 20 at 14:34
• my bad, updated the question. @siggemannen – Daniel B Commented Feb 20 at 14:38
• 1 UPDATE statements aren't iterative. If are expecting the UPDATE to run row by row; it doesn't. It does the UPDATE in one go, so if you attempt to UPDATE the same row many times, you UPDATE it once, and an arbitrary value is used. – Thom A Commented Feb 20 at 14:41
• 1 there was another question which dealt with multiple array values insertions this week, should look for it. SQL Server kinda sucks at this, so your best shot is probably to reconstruct the whole array and then modify the original json with it – siggemannen Commented Feb 20 at 14:43
• 2 I assume you mean this one @siggemannen . – Thom A Commented Feb 20 at 14:56

 |  Show 2 more comments

2 Answers 2

Sorted by: Reset to default 2

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

The problem is in two parts.

First, look at this code:

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

The A, B syntax in the FROM clause is actually doing a JOIN using syntax that has been obsolete for more than 30 years. And with no matching criteria, it's equivalent to a CROSS JOIN, giving you every possible combination of record from both tables.

You should not use this syntax.

If you want a JOIN, always use the newer ANSI join syntax. Either of these is equivalent (and preferred!) vs the original code:

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

OR

SELECT u.*, s.*    -- (10 rows affected)
FROM @Setting s
INNER JOIN @newUrls u ON 1=1

We have the same situation in the UPDATE statement. The difference there is, because you are doing an UPDATE, only the original table matters. There are only two rows there, so only two rows can be affected.

---

This leads me to the second issue.

If you want to add the @newUrls data as new rows in the @Settings table, this is NOT an UPDATE query. Think about the mnemonic UPDATE as referring specifically to the existing rows in the named table.

only '/test3' got appended

There is no way an UPDATE can ever append any new rows. It can only modify existing rows.

Instead, you need to do an another INSERT.

Exactly what this insert looks like depends on how you want to treat the Id column. Since I don't see an identity or sequence anywhere, you'll likely need additional code to handle it, and lacking clearer indication of what you may be working with I'm loathe to just write this for you.

---

But I also see the append command in JSON_MODIFY. Perhaps you want the new data to line up with existing rows.

In that case, your situation is even worse, because you have no matching criteria for what new value goes where with what existing rows. Remember: SQL tables are unordered by definition. SQL databases will NEVER match up rows from distinct sets by row order, unless you have done work in the SQL code to formally define that row order.

At very least, in this situation the question needs additional clarification for what the final results need to look like and why you expect that result.

Also understand: a single statement can either modify existing rows or insert new rows. You cannot do both in the same statement. (MERGE kind of does this, but still has you build separate UPDATE/INSERT commands to use based on the matching).