How to avoid updating JSON special characters when changing JSON name using SQL script?

Viewed 25

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/10"},{"test":"D", "value": "1/10"},{"test":"E", "value": "3"}]

I have the script to change the header key to be test but it is changing the value '/' to be '/' which I do not want since I am taking these values and displaying them elsewhere and I want only the '/' symbol.

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

That script changes my JSON to look like this:

|Columns|
--
[{"test":"", "value": 1},{"test":"", "value": 2},{"test":"", "value": 3}]
[{"test":"C", "value": "1\/10"},{"test":"D", "value": "1\/10"},{"test":"E", "value": "3"}]

I want it to look like this:

|Columns|
--
[{"test":"", "value": 1},{"test":"", "value": 2},{"test":"", "value": 3}]
[{"test":"C", "value": "1/10"},{"test":"D", "value": "1/10"},{"test":"E", "value": "3"}]
0 Answers
Related