时间戳

首页 > 解决方案 > 如何修改json subArray sql server中的特定对象

sql - 如何修改json subArray sql server中的特定对象

问题描述

我在 sql db 中有 json 字符串，因为

{
   "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"
            }
         }
      ]
   }
}

您可以在https://jsonparseronline.com/上解析以查看 json

我需要更新$.MainItem.ListSubItemswhere UserId=3 set UserName ='new name'

我需要更新所有用户 ID = 3

作为

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

标签： sqljsonsql-servertsqlsql-update

解决方案

如果我遵循您正在尝试做的事情，这应该可以。我不知道您的表中有多少记录，因此可能需要进行一些调整/调整。

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

推荐阅读