CODEWITHSUNDEEP
sqlsql-server
Sirth
Sirth

Reputation: 99

Is there a way to replace the JSON value and set it to '' at the same time?

I have a JSON field the looks like this

  [{"header":"C", "value": 1"},{"header":"D", "value": 2},{"header":"E", "value": 3}]

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":"", "value": 1},{"test":"", "value":2},{"test":"", "value": 3}]

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

-EDIT - how do I keep the 2nd value the same while also clearing the first value and changing the name of it?

Upvotes: 1

Views: 835

Answers (2)

Charlieface
Charlieface

Reputation: 72090

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 = ''
      -- more properties here
    FROM OPENJSON(Columns)
      WITH (
        header nvarchar(10)
        -- more properties here
      ) j
    FOR JSON PATH
);

db<>fiddle

Upvotes: 1

Srinivasan Rajasekaran
Srinivasan Rajasekaran

Reputation: 585

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

Upvotes: 0

Related Questions