CODEWITHSUNDEEP
sqljsonsql-servert-sqlsql-update
Tarek
Tarek

Reputation: 115

How Modify specific object in json subArray sql server

I have json string in sql db as this 

{
   "MainItem":{
      "UserId":4,
      "UserName":"name",
      "CityDetails":{
         "CityId":1,
         "CityName":"egypt"
      },
      "ListSubItems":[
         {
            "UserId":2,
            "UserName":"name2",
            "FullDescription":"",
            "ShortDescription":"wish",
            "CityDetails":{
               "CityId":2,
               "CityName":"us"
            }
         },
         {
            "UserId":3,
            "UserName":"name3",
            "FullDescription":"",
            "ShortDescription":"wish",
            "CityDetails":{
               "CityId":44,
               "CityName":"sau"
            }
         }
      ]
   }
}

you can parse on https://jsonparseronline.com/ to view json

I need to update $.MainItem.ListSubItems where UserId=3 set UserName ='new name'

I need update all userid = 3

as 

update MyTable 
  set jsonCol= json_modify(jsonCol, $.MainItem.ListSubItems .where userId=3 , userName='new name ')

Upvotes: 0

Views: 51

Answers (1)

Aaron Hughes
Aaron Hughes

Reputation: 476

If i'm following what you are trying to do, this should work. I don't know how many records are in your table so some tweaking/tuning may be necessary. 

DECLARE @MyTable TABLE
(
    ID INT IDENTITY (1,1) NOT NULL
    ,JsonCol NVARCHAR(MAX)
)

INSERT INTO @MyTable
(
    JsonCol
)
VALUES
(N'{
   "MainItem":{
      "UserId":4,
      "UserName":"name",
      "CityDetails":{
         "CityId":1,
         "CityName":"egypt"
      },
      "ListSubItems":[
         {
            "UserId":2,
            "UserName":"name2",
            "FullDescription":"",
            "ShortDescription":"wish",
            "CityDetails":{
               "CityId":2,
               "CityName":"us"
            }
         },
         {
            "UserId":3,
            "UserName":"name3",
            "FullDescription":"",
            "ShortDescription":"wish",
            "CityDetails":{
               "CityId":44,
               "CityName":"sau"
            }
         }
      ]
   }
}'
)


UPDATE @MyTable
Set JsonCol = 
    JSON_MODIFY
        (jsonCol
        ,'$.MainItem.ListSubItems'
        ,(select 
        orig.UserId
        ,COALESCE(NewVals.UserName, orig.UserName) as UserName
        ,orig.FullDescription
        ,orig.ShortDescription
        ,orig.CityDetails
    from OPENJSON(jsonCol, '$.MainItem.ListSubItems') 
    WITH (
        UserId INT 
        ,UserName NVARCHAR(100)
        ,FullDescription NVARCHAR(1000)
        ,ShortDescription NVARCHAR(250)
        ,CityDetails NVARCHAR(MAX) as json
    ) as orig
    left join 
        (
            SELECT 3 as UserID, 'new name' as UserName
        ) as NewVals ON orig.UserId = NewVals.UserID
    FOR JSON AUTO)
    )

SELECT * FROM @MyTable

Upvotes: 2

Related Questions