How to update a text field with broken JSON literals in PostgreSQL?

Question

I have a lot of character varying records in this format: {'address': 'New Mexico'}.
I would like to update all those columns to have it like this: New Mexico.

I've been investigating how to do it, and it could be with regexp, but I don't know how to make for all columns in a table, and I never used regex in PostgreSQL before.

I have an idea that is something like this:

SET location = regexp_replace(field, 'match pattern', 'replace string', 'g')

Answer 1

Valid JSON literals require double-quotes where your sample displays single quotes. Maybe you can fix that upstream?

To repair (assuming there are no other, unrelated single-quotes involved):

UPDATE web_scraping.iws_informacion_web_scraping
SET    iws_localizacion = replace(iws_localizacion, '''', '"')::json ->> 'address'
WHERE  iws_id = 3678
AND    iws_localizacion IS DISTINCT FROM replace(iws_localizacion, '''', '"')::json ->> 'address';

The 2nd WHERE clause prevents updates to rows that wouldn't change. See:

• How do I (or can I) SELECT DISTINCT on multiple columns?

Optional if such cases can be excluded.