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?

I removed the Postgres tag as JSON_MODIFY() is a SQL Server function. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 2, 2017 at 5:00
ahh yeah sorry. I left it in there originally as this data comes in from a Postgres jsonb, but probably not so relevant as the solution as to be in SQL Server — onji
– onji, Commented Mar 2, 2017 at 5:05
If I could change things on the Postgres side I'd change many many things — onji
– onji, Commented Mar 2, 2017 at 23:12

Jovan MSFT · Accepted Answer · 2017-03-02 19:46:22Z

10

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)

answered Mar 2, 2017 at 19:46

Jovan MSFT

14.8k4 gold badges44 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

onji Over a year ago

This works great & thanks for the tip about setting a value to NULL to delete a key!

Collectives™ on Stack Overflow

Replace/Rename/Modify JSON key in SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related