1

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?

blackgreen
  • 34,072
  • 23
  • 111
  • 129
Sirth
  • 89
  • 6
  • In the `REPLACE` function? No. Are there properties other than `"header"` in the original JSON that you need to keep? Seems like you could use `FOR JSON` to generate replacement JSON data. – AlwaysLearning Sep 22 '22 at 04:58
  • Don't change the question after people have posted anwers, it's not appreciated. Make a new question if you have a new problem. – Charlieface Sep 22 '22 at 22:51
  • The \/ is not a problem, it's perfectly valid JSON escaping. When you parse it again using `OPENJSON` or another parser you will see it's gone. You should declare `value int` otherwise you will get `""` quotes around it. – Charlieface Sep 22 '22 at 22:52
  • I don't want the \/ to appear because I am parsing the values and put it into an excel file and it is currently getting the \/ when I just want /. So like 1/10 is expected but I am getting 1\/10 @Charlieface – Sirth Sep 23 '22 at 21:27
  • As I said, that is standard JSON escaping (which you can see here https://stackoverflow.com/a/27516892/14868997 or many other places). I don't know what parser you are using, but no parser worth it's salt will parse or encode it any other way. You can see also here https://www.freeformatter.com/json-escape.html for example how it should be done. – Charlieface Sep 25 '22 at 15:28

2 Answers2

1

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

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • I made an edit to the JSON and it is taking away the value and replacing it with {"test"='"'}. How do I keep the value as well? @Charlieface – Sirth Sep 22 '22 at 16:42
  • See edit. You need to add all of the original properties to the select – Charlieface Sep 22 '22 at 16:44
  • I made another edit - when I have 1/10 in as a value, the script changes it to 1\/10 for some reason @Charlieface – Sirth Sep 22 '22 at 17:16
  • Also that is clearing out the existing tests if I run the script again- is there a way to only run the script only if header exists ? – Sirth Sep 22 '22 at 22:12
0
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
Srinivasan Rajasekaran
  • 555
  • 3
  • 17