Update JSON array using SQL Server

Question

I have JSON like below:

{
 "property": {
  "commonProperty": "abc",
  "Items": [
   {
    "ID": 1,
    "Name": "a"
   },
   {
    "ID": 2,
    "Name": "a"
   },
   {
    "ID": 3,
    "Name": "b"
   }
  ]
 }
}

And what I want to achieve is to update Names to "c" where it's currently "a" using SQL Server (so I want to have result like below).

{
 "property": {
  "commonProperty": "abc",
  "Items": [
   {
    "ID": 1,
    "Name": "c"
   },
   {
    "ID": 2,
    "Name": "c"
   },
   {
    "ID": 3,
    "Name": "b"
   }
  ]
 }
}

As far as I know I cannot use JSON_MODIFY because it does not handles queries inside it's arguments and OPENJSON cannot be updated. Is there any method I can use?

EDIT note: added common property above Items.

Would the "dumb" solution of just replacing "Name": "a" with "Name": "c" using REPLACE work for you? Obviously not in general since there could be any number of objects with a Name property, but for specific scenarios you can get away with it (possibly by selecting the array separately first, then replacing it as a whole). — Jeroen Mostert
– Jeroen Mostert, Commented Aug 4, 2020 at 13:35
Unfortunately it's only representation of problem that exists in huge database with way more complex JSONs. Otherwise it would be helpful though so thank you for an answer. — TajniakOsz
– TajniakOsz, Commented Aug 4, 2020 at 16:11

Zhorov · Accepted Answer · 2020-08-07 14:25:51Z

4

You may try one of the following options:

Parse the '$.property.Items' JSON array as table using OPENJSON(), make an update, output the table's content as JSON using FOR JSON and modify the original JSON using JSON_MODIFY():
Build a dynamic statement. You can modify the input JSON using JSON_MODIFY() and the appropriate path. The path needs to be defined as a literal or from SQL Server 2017 as a variable, but using a wildcard is not possible (e.g. the statement SELECT @json = JSON_MODIFY(@json, '$.property.Items[0].Name', N'c') modifies the first item in the Items JSON array).

JSON:

DECLARE @json nvarchar(max) = N'{
   "property":{
      "commonProperty":"abc",
      "Items":[
         {
            "ID":1,
            "Name":"a"
         },
         {
            "ID":2,
            "Name":"a"
         },
         {
            "ID":3,
            "Name":"b"
         }
      ]
   }
}'

Statement (that parses the JSON as a table, modifies the table and outputs the table's content as JSON):

SELECT @json = JSON_MODIFY(
   @json,
   '$.property.Items',
   (
   SELECT 
      ID AS 'ID', 
      CASE WHEN Name = N'a' THEN N'c' ELSE Name END AS 'Name'
   FROM OPENJSON(@json, '$.property.Items') WITH (ID int, Name nvarchar(50))
   FOR JSON PATH
   )
)

Dynamic statement:

DECLARE @stm nvarchar(max)
SET @stm = (
   SELECT N'SET @json = JSON_MODIFY(@json, ''$.property.Items[' + [key] + N'].Name'', N''c''); '
   FROM OPENJSON(@json, '$.property.Items')
   WHERE JSON_VALUE([value], '$.Name') = N'a'
   FOR XML PATH('')
)

PRINT @stm
EXEC sp_executesql @stm, N'@json nvarchar(max) OUTPUT', @json OUTPUT

edited Aug 7, 2020 at 14:25

answered Aug 4, 2020 at 13:44

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TajniakOsz Over a year ago

Hi, thank you for your answer. First option worked for me, I didn't have time to test the second one yet. The only problem is that if there is additional common property on "property" level (edited initial question) - it's being lost with the first option. I tried few changes but these did not work for me. How should the query be modified to preserve it?

Zhorov Over a year ago

@TajniakOsz Just change the JSON_MODIFY() path expression and remove PATH(''). The answer is updated. Thanks.

Collectives™ on Stack Overflow

Update JSON array using SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related