Postgres Increment JSONB Object Property By X-CodePudding

I have the following JSONB object in column "charges" of table "Reservation".

{"total": 0, "charges": [{"foo": "bar"}]}

What's the SQL statement to increment (update) the value of total by X without modifying rest of the JSONB structure?

CodePudding user response：

Sample and query structure in: dbfiddle

select
  data as before,
  data || jsonb_build_object('total', (data ->> 'total') :: numeric   3) as after
from
  test

Or if you want update table you can use this sample:

update your_table
set your_josnb_column = your_josnb_column || jsonb_build_object('total', (your_josnb_column ->> 'total') :: numeric   3)