CODEWITHSUNDEEP
sqljsonsql-server
TajniakOsz
TajniakOsz

Reputation: 91

Update JSON array using SQL Server

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.

Upvotes: 2

Views: 4969

Answers (1)

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 4

Related Questions