CODEWITHSUNDEEP
sqlsql-servert-sqlfor-json
Sirth
Sirth

Reputation: 99

How to run SQL script only on JSON keys that exist

I have JSON fields the looks like this

Columns
[{"header":"C", "value": 1},{"header":"D", "value": 2},{"header":"E", "value": 3}]
[{"test":"C", "value": 1},{"test":"D", "value": 2},{"test":"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:

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

I have the script to change the header key to be test but it is clearing the existing JSON key for test that have values in them. How do I keep the 2nd value the same while also clearing the first value and changing the name of it? Do I need to add a WHERE header exists?

UPDATE Files
SET Columns = (
    SELECT test = '', value
     
    FROM OPENJSON(Columns)
      WITH (
        header varchar(50),
        value varchar(50)
      ) j
    FOR JSON PATH
);

Upvotes: -1

Views: 99

Answers (1)

Zhorov
Zhorov

Reputation: 29943

You may try to modify your second statement and include an additional test column in the explicit schema:

UPDATE Files
SET Columns = (
   SELECT COALESCE(test, '') AS test, value
   FROM OPENJSON (Columns) WITH (
      test varchar(50) 'lax $.test',
      value int 'lax $.value'
   )
   FOR JSON PATH
)

Upvotes: 0

Related Questions