CODEWITHSUNDEEP
jsonpostgresql
Brian
Brian

Reputation: 13573

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

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?

Upvotes: 4

Views: 4078

Answers (2)

dwir182
dwir182

Reputation: 1549

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

Upvotes: 2

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

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

Upvotes: 6

Related Questions