Psql Update query to replace json data key(stored as json field)

Hello Guys,

   In one of my project,  I need to replace json key without changing the values in the psql. I could able to find replace text in psql side. We need to write separate function to update the json key.

For my purpose, i have googled around and find the idea or converting the field as text and convert back to json was easier and better(in my project).

If we are not converting to text type, psql will wont allow you to replace string in json field format.


Here is the snippet for it.

Please note, data field is json type

UPDATE table_name SET <field_name> = REPLACE(<field_name>::TEXT, 'old_string', 'new_string')::json;

eg:- UPDATE customer_details SET data = REPLACE(data::TEXT, '"map":', '"maps":')::json;


You can also replace for jsonb type as well by replacing above ::json with ::jsonb



Please note, this is my personal opinion and there might be other best ways to do the same stuff.


Hope!! this will help few folks


Cheers coding!!!