Modifying existing field in json column in Postgres

Question

So I am trying to update some entries in my postgres database. In particular I am trying to modify a field value using the existing value. For example, say I have the following json

{"var1": 10, "var2": 0.003, "var3": null}

and I want to update var2 to var2*100. I have updated values using an update statemnt, e.g.

UPDATE my_table SET json_column = jsonb_set(my_column, '{var2}', '0.003', true) WHERE (my_column->'var2') is null;

so I am trying to use an equivalent statement

UPDATE my_table SET my_column = jsonb_set(json_column, '{var2}', '(json_column->'var2)::double precision*100', true) WHERE id = 12;

however I am facing syntax errors. Has anyone tried something like this?

NuLo · Accepted Answer · 2021-09-03 19:24:00Z

1

This can get really tricky but you can get there with some casts. It worked for me like this:

UPDATE my_table
SET my_column = jsonb_set(json_column, '{var2}', to_jsonb((json_column->'var2')::double precision*100), true)
WHERE id = 12;

answered Sep 3, 2021 at 19:24

NuLo

1,5381 gold badge13 silver badges19 bronze badges

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

1 Comment

DatGuy Over a year ago

Yes. I knew it was something like that but I couldn't get the syntax correctly. Thank you very much

Collectives™ on Stack Overflow

Modifying existing field in json column in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related