SQL JSON update

Question

If I have this JSON

{
      "hello1": "1",
      "hello2": "2"
}

and I want to update it from this JSON

{
      "hello2": "3"
}

I want the result to be

{
      "hello1": "1",
      "hello2": "3"
}

Is there a way to do this without the need to go through every field in SQL?

My current attempt is to update every value manually like this:

SET @OldJSON = JSON_MODIFY(
   @OldJSON,
   '$.hello1', 
   ISNULL(JSON_VALUE(@JSON,'$.hello1'), JSON_VALUE(@OldJSON,'$.hello1'))
)

@Hasan What do you mean by `...without the need to go through every field...`? — Zhorov, Dec 01 '20 at 08:07
I didn't attempt anything, I was going to update every value manually like this, `SET @OldJSON = JSON_MODIFY(@OldJSON,'$.hello1', ISNULL(JSON_VALUE(@JSON,'$.hello1'), JSON_VALUE(@OldJSON,'$.hello1')))` but I'm hoping to find some kind of dynamic way which I would only say `SET @OldJSON = SomeKindOfMethodHere(@JSON)` — Hasan, Dec 01 '20 at 08:23
@Hasan Does the new JSON always contain one `key\value` pair and what is your SQL Server version? — Zhorov, Dec 01 '20 at 09:49
@Zhorov the new JSON can contain some or all of the original object fields and the version that I'm using is 18.3.1 — Hasan, Dec 01 '20 at 10:15
@Hasan, `18.3.1` is probably the version of SQL Server Managament Studio (which is a client tool). What does `SELECT @@VERSION` return? — Zhorov, Dec 01 '20 at 10:16
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor) — Hasan, Dec 01 '20 at 10:40
Does this answer your question? [Concatenate or merge two json objects in SQL Server](https://stackoverflow.com/questions/48911530/concatenate-or-merge-two-json-objects-in-sql-server) — JeffUK, Dec 01 '20 at 11:16
@JeffUK no this will add 2 JSON together, my scenario is that I have 2 JSON that are generated from the same object which means both of them might have all or some of the original object fields, and in the case of update the user might only provide me with some or all of the fields, and in case I got some of the fields I need to keep whatever data in the rest of the fields that the user didn't provide me with and change what they gave me, I was wondering if there is an easy and fast way to do so without the need to write every field in my object one by one — Hasan, Dec 01 '20 at 13:08

Zhorov · Accepted Answer · 2020-12-03T08:52:13.127

Possible options to get the expected results are:

JSON_MODIFY() and SELECT statement
JSON_MODIFY() and dynamic statement

JSON_MODIFY() and SELECT statement:

As is mentioned in the documentation, in SQL Server 2017 (14.x) and in Azure SQL Database, you can provide a variable (or an expression in this case) as the value of path. So you can easily build a path for each of the new key/value pairs.

DECLARE @old varchar(1000) = '{"hello1":"1","hello2":"2","hello3":"3"}'
DECLARE @new varchar(1000) = '{"hello2":"a","hello3":"b"}'

SELECT @old = JSON_MODIFY(
   @old,
   CONCAT('$."', n.[key], '"'),
   COALESCE(n.[value], o.[value])
)
FROM OPENJSON(@old) o
JOIN OPENJSON(@new) n ON n.[key] = o.[key]

JSON_MODIFY() and dynamic statement:

The scenario is almost the same, but each update is in separate statement, generated dynamically:

DECLARE @old varchar(1000) = '{"hello1":"1","hello2":"2","hello3":"3"}'
DECLARE @new varchar(1000) = '{"hello2":"a","hello3":"b"}'

DECLARE @stm nvarchar(max)
SELECT @stm = STRING_AGG(
   CONCAT(
      'SELECT @old = JSON_MODIFY(@old, ''$."', n.[key], '"'', ''',
      COALESCE(n.[value], o.[value]), ''')'
   ),
   '; '
)   
FROM OPENJSON(@old) o
JOIN OPENJSON(@new) n ON n.[key] = o.[key]

DECLARE @err int
EXEC @err = sp_executesql @stm, N'@old varchar(1000) OUTPUT', @old OUTPUT
IF @err = 0 PRINT 'Success' ELSE PRINT 'Error'

Result:

Both approaches return the following JSON:

{"hello1":"1","hello2":"a","hello3":"b"}

Adding new key\value pair(s):

If the new JSON content inludes new key\value pair(s), simply use FULL JOIN:

DECLARE @old varchar(1000) = '{"hello1":"1","hello2":"2","hello3":"3"}'
DECLARE @new varchar(1000) = '{"hello2":"a","hello4":"b"}'

SELECT @old = JSON_MODIFY(
   @old,
   CONCAT('$."', n.[key], '"'),
   COALESCE(n.[value], o.[value])
)
FROM OPENJSON(@old) o
FULL JOIN OPENJSON(@new) n ON n.[key] = o.[key]

this is exactly what I need, but its missing one thing which is if I have a new field in the new JSON it will not be carried out to the old one like lets say I have "hello4" in the new JSON if I run this the OldJSON will be updated but the "hello4" field will not be added. but this is great and helpful thank you so much — Hasan, Dec 03 '20 at 08:48

SQL JSON update

1 Answers1