How to update json data stored in a text field in PostgreSQL?

Question

I have a table services which has a column named properties whose type is text.

There are json data stored in properties column.

I can query json data with psql like this:

SELECT * FROM services WHERE properties::json->>'url' = 'www.example.com';

But I cannot update the json data with the following query:

UPDATE 
   services SET properties::json->>'url' = 'www.mydomain.com' 
WHERE 
   properties::json->>'url' = 'www.example.com';

The UPDATE command above generates the error:

ERROR:  syntax error at or near "::"
LINE 1: UPDATE services SET properties::json->>'url' = 'www.....

How can I update the url field inside the properties column?

What's wrong with my UPDATE command?

Answer 1

You should store the records as JSON instead of TEXT so that type casting won't be required at all places.

If you are running Postgres 9.5+, you could make use of jsonb_set

UPDATE 
   services SET properties = jsonb_set(properties::jsonb,
                 '{url}' , '"www.mydomain.com"' )::text
WHERE 
   properties::json->>'url' = 'www.example.com';

Demo

Answer 2

I don't know the structure of your JSON so i am use my sample.

You can use REPLACE function to REPLACE string example with domain and after that you cast to JSON but it would be good you change your properties type column text to json.

REPLACE Definition:

Replace all occurrences in string of substring from with substring to

update services
set properties = replace(properties,'example','domain')::jsonb
where properties::json->>'url' = 'www.example.com';

You can see here Demo