Replace/Rename/Modify JSON key in SQL Server

Question

I've got a column called attributes which contains a JSON blob. This blob can have single and multiple key:values in a single row.

Here's 3 rows of simplified sample data:

{"68c4":["yes"],  "c8ew":["0","1"],  "p6i4":["London","Frankfurt","Tokyo"]}
{"472h":["USD"],  "c8ew":["-1","9"],  "p6i4":["New York"]}
{"472h":["EUR","JPY"]}

The key's are UUIDs and I need to replace these with the human readable component

I know I can write something like:

SELECT JSON_MODIFY(attributes, '$."p6i4"', 'City') AS modified

But this changes the value. My problem is I need to change the key. Does anyone know how to do this?

Answer 1

You can insert new key with the value/fragment of old key and delete old key:value.

This code will add new 'City' key with the value from 'p6i4':

SELECT JSON_MODIFY(attributes, '$.City', JSON_QUERY(attributes, '$.p6i4'))

Note that you need to use JSON_QUERY because you have array values. If you set NULL value in old key after you copy it in another key, JSON_MODIFY will delete it:

SELECT JSON_MODIFY(
            JSON_MODIFY(attributes, '$.City', JSON_QUERY(attributes, '$.p6i4')),
       '$.p6i4', NULL)