Concat value to JSON array elements via iteration in SQL Server

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:

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:

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.

>Solution :

Here’s a little quick n dirty solution:

SELECT  '[' + string_agg(quotename(ISNULL(prefixes.p, '??') + y.value, '"'), ',') within GROUP(ORDER BY sort, cast(y.[key] AS int)) + ']'
FROM    (
    SELECT  *
    ,   row_number() OVER(ORDER BY @@spid) AS sort
    FROM    openjson('{"Klaus":["ABC","DEF","GHI"],"Herbert":["ACC","DLK","HOP"]}') x
    ) x
CROSS apply openjson(x.value) y
LEFT JOIN (
    VALUES  ('Klaus', '01')
    ,   ('Herbert', '02')
    -- Add more
    ) prefixes (k, p)
    ON  prefixes.k = x.[key]

1. OPENJSON on a json will generate a key:value row set which we can use to JOIN on the prefix-table to get the prefixes.
2. The second OPENJSON on the array will split the elements, which we then reassemble using STRING_AGG into an array.
3. I assume all elements are strings, if not, there needs to be some more work done.

Note that inherit order of keys in openjson isn’t really guaranteed to be stable, so if you want Klaus to always be ahead of Herbert, perhaps you need to add better sort than @@spid.