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?

Can you share sample data from json?

dwir182
– dwir182

2018-12-14 03:55:29 +00:00
Commented Dec 14, 2018 at 3:55 — dwir182
– dwir182, Commented Dec 14, 2018 at 3:55

Kaushik Nayak · Accepted Answer · 2018-12-14 04:39:55Z

6

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

answered Dec 14, 2018 at 4:39

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Comments

dwir182 · Accepted Answer · 2018-12-14 05:57:33Z

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

edited Dec 14, 2018 at 5:57

answered Dec 14, 2018 at 4:36

dwir182

1,54911 silver badges21 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related