I have a JSON field the looks like this

  [{"header":"C"},{"header":"D"},{"header":"E"}]

I want to change the name of the JSON value header and set it to '' but I was only able to change the value.

  UPDATE Files SET Columns = REPLACE(Columns, '"header":', '"test":')

I want it to look like this:

  [{"test":""},{"test":""},{"test":""}]

Is there a way to set the renamed values to be "" in the REPLACE function?

CodePudding user response：

You would need to rebuild the JSON using OPENJSON to break it open and FOR JSON PATH to rebuild it

UPDATE Files
SET Columns = (
    SELECT test = ''
    FROM OPENJSON(Columns)
      WITH (
        header nvarchar(10)
        -- more properties here
      ) j
    FOR JSON PATH
);

db<>fiddle

CodePudding user response：

DECLARE @Json VARCHAR(200)='[{"header":"C"},{"header":"D"},{"header":"E"}]'



SELECT '[' STRING_AGG(JsonVal,',') ']'
FROM (
SELECT A.JsonVal FROM OPENJSON(@Json)
CROSS APPLY(SELECT '{"test":""}' AS JsonVal) A ) J