0

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?

user15178581
  • 57
  • 1
  • 4

1 Answers1

2

demo:db<>fiddle

UPDATE mytable                                    -- 6
SET mydata = jsonb_set(                           -- 4
    mydata::jsonb,                                -- 1
    '{john}',                                     -- 2
    ((mydata ->> 'john')::int + 1)::text::jsonb   -- 3
)::json;                                          -- 5
  1. Fetch your data. If it is of type json, cast it into type jsonb
  2. Path to your requested element as text array
  3. 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
  4. Use jsonb_set() to update the JSON object specified in (1)
  5. If your column is of type json instead of jsonb, cast the result back into type json
  6. Perform the update
S-Man
  • 22,521
  • 7
  • 40
  • 63
  • Thank you. What if i have a json object like this: {'john': {'age': 1}, 'alex' : {'age': 4}, 'harry' : {'age': 2}} – user15178581 Mar 15 '21 at 18:15
  • It's similar. You just have to modify the path which points the object: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=55f0d456699e56e03028a7381e48af39 `jsonb_set(mydata::jsonb, '{john}', ((mydata ->> 'john')::int + 1)::text::jsonb)::json` – S-Man Mar 15 '21 at 19:18