Is there an UPDATE equivalent command to SELECT json data-CodePudding

Is there a way to update the data retrieved from the below select (in this case, change "MS220" to something else)? It's difficult enough to do select from JSON in some cases. I'm not sure how to update just a single element.

CREATE TABLE JData (
   JsonData nvarchar(max)
)
INSERT INTO JData
   (JsonData)
VALUES 
   ('[
    {
        "Categories": [
            {
                "QuerySourceNames": [
                    "QAsset"
                ],
                "Id": "eceae85a-ffc6-49f4-8f6a-78ce2b4b274e",
                "Name": "emsdba"
            }
        ],
        "Id": "525b4f07-0f67-43ac-8070-a0e6c1ceb1b9",
        "Name": "MS220"
    }
]')

SELECT
    ParamName
FROM [dbo].[JData] jsonData
CROSS APPLY OPENJSON (jsonData)
WITH 
(
  Categories nvarchar(max) AS json,
  Id uniqueidentifier,
  ParamName varchar(10) '$.Name'
);

CodePudding user response：

Try JSON_MODIFY() with the path '$[0].Name'

UPDATE d
SET   jsonData = JSON_MODIFY(jsonData, '$[0].Name', 'New Value')
FROM [dbo].[JData] d

Results:

[
    {
        "Categories": [
            {
                "QuerySourceNames": [
                    "QAsset"
                ],
                "Id": "eceae85a-ffc6-49f4-8f6a-78ce2b4b274e",
                "Name": "emsdba"
            }
        ],
        "Id": "525b4f07-0f67-43ac-8070-a0e6c1ceb1b9",
        "Name": "New Value"
    }
]

db<>fiddle here