I’m migrating a column to a different JSON structure and am struggling on doing that.
The initial value that is already in the database looks as following:
| MyColumn |
|---|
| {“Klaus”:[“ABC”,”DEF”,”GHI”],”Herbert”:[“ACC”,”DLK”,”HOP”]} |
The migration aims to get rid of the nesting and instead of the keyword a prefix for the values should be used. The desired output therefore looks like the following:
| MyColumn |
|---|
| [“01ABC”,”01DEF”,”01GHI”,”02ACC”,”02DLK”,”02HOP”] |
My attempt would be to first split up the information based on the keyword and put that into separate columns (the amount of keywords and keywords themselve are fixed):
ALTER TABLE databasetable ADD TemporaryColumnKlaus nvarchar(max)
ALTER TABLE databasetable ADD TemporaryColumnHerbert nvarchar(max)
UPDATE databasetable SET TemporaryColumnKlaus = JSON_QUERY(MyColumn, '$.Klaus') WHERE MyColumn IS NOT NULL AND MyColumn != '{}'
UPDATE databasetable SET TemporaryColumnHerbert = JSON_QUERY(MyColumn, '$.Herbert') WHERE MyColumn IS NOT NULL AND MyColumn != '{}'
Now I’m unsure how I can access each property in the JSON structure and concatenate the value “01” or “02” to each property.
How can that be done easily? I tried using JSON_MODIFY but have no clue how to iterate over each property.