Updating json columns in psql

Question

I have a table containing a json column. The json values will look something like this:

{'john': 1, 'alex' : 4, 'harry' :2}

If I wanted to add 1 to john, how would I go about doing this?

S-Man · Accepted Answer · 2021-03-15 16:18:45Z

UPDATE mytable -- 6 SET mydata = jsonb_set( -- 4 mydata::jsonb, -- 1 '{john}', -- 2 ((mydata ->> 'john')::int + 1)::text::jsonb -- 3 )::json; -- 5

Fetch your data. If it is of type json, cast it into type jsonb
Path to your requested element as text array
Fetch the original value. ->> operator returns type text, so to do an integer operation, you need to cast it into type int. Then add the 1. This result must be reconverted into type jsonb. Unfortunately type int cannot be cast into type jsonb directly, so take the intermediate step via type text
Use jsonb_set() to update the JSON object specified in (1)
If your column is of type json instead of jsonb, cast the result back into type json
Perform the update

Thank you. What if i have a json object like this: {'john': {'age': 1}, 'alex' : {'age': 4}, 'harry' : {'age': 2}}
It's similar. You just have to modify the path which points the object: dbfiddle.uk/… jsonb_set(mydata::jsonb, '{john}', ((mydata ->> 'john')::int + 1)::text::jsonb)::json

1 Answer 1