How to run SQL script only on JSON keys that exist

Question

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
);

The input JSON is not valid JSON due to the unbalanced `"` characters. — AlwaysLearning, Sep 23 '22 at 00:34
Seems like `OPENJSON` needs to account for the possibility of either `header` or `test` properties being present in the source JSON, then you need to `COALESCE` those properties in the select instead of just assiging `test = ''`. — AlwaysLearning, Sep 23 '22 at 00:39
As per my comment, `"value": 1"` is not a valid JSON property declaration. — AlwaysLearning, Sep 23 '22 at 00:49
That seems to be a typo. Should be something like: SELECT COALESCE(header, test=''), value — Sirth, Sep 23 '22 at 00:56
What is the exact data-type of your `Columns` column? Is there a _good reason_ why you're storing JSON like this btw, instead of denormalizing it to relations (tables)? — Dai, Sep 23 '22 at 00:57
Why do you have two separate JSON arrays in your first code-block? Please clarify **exactly** what you want to accomplish because your question's text does not match your posted JSON (e.g. you wrote " want to change the name of the JSON value header and set it to ''") when your code shows you changing `"header"` to `"test"` so I'm just confused. — Dai, Sep 23 '22 at 01:00
The JSON already exists in my database. I want to change header to test and also clear the values of header but keep the values if test is already there — Sirth, Sep 23 '22 at 01:04
The two separate JSON arrays are 2 different rows of JSON arrays if that is what you are confused about — Sirth, Sep 23 '22 at 01:26
Does this answer your question? [Is there a way to replace the JSON value and set it to '' at the same time?](https://stackoverflow.com/questions/73809185/is-there-a-way-to-replace-the-json-value-and-set-it-to-at-the-same-time) — Dai, Sep 24 '22 at 21:55

Zhorov · Answer 1 · 2022-09-23T08:49:47.397

0

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
)

edited Sep 23 '22 at 08:49

answered Sep 23 '22 at 05:55

Zhorov

28,486
6
27
52

`lax` is the default. The column name is by default used as the property name so no need to specify it. – Charlieface Sep 23 '22 at 10:51
It is changing JSON values that are set to / to \/ : [{"test":"", "value": 1/10}] is turning into [{"test":"", "value": 1\/10}]. Is there a way to prevent this to keep the original value? – Sirth Sep 23 '22 at 21:52

How to run SQL script only on JSON keys that exist

1 Answers1