json - Math calculating with two JSONB keys in Postgres? -

i have jsonb column data like:

{"plays": {"win": 90, "draw": 8, "lose": 2}} 

how can calculate sum win , draw keys?

something like:

select     data::json#>>'{plays,draw}' + data::json#>>'{plays,win}' "total_plays",     plays_data; 

let's assume table , data following ones (note have avoided called json column, avoid confusion between column names , types; recommended practice):

create table data (     some_data json ) ;  insert data     (some_data) values     ('{"plays": {"win": 90, "draw": 8, "lose": 2}}') ; 

you need use following query:

select     cast(some_data->'plays'->>'win' integer) + cast(some_data->'plays'->>'draw' integer) total_plays     data ; 

 | total_plays | | ----------: | |          98 | 

explanation:

1. -> operator, applied json column (a json object) on left, , string on right, gives corresponding field json object (which might object, array or value).

2. ->> operator gives field value text. postgresql doesn't have way know whether data string, number or boolean; treats strings, how they're stored.

3. cast(expression type) converts expression specified type. javascript might use number or string , cast 1 other needed. postgresql must told explicitly of times whether expressions needs interpreted 1 or other. numbers, javascript doesn't let specify between floats or integers. postgresql needs specific.

you can check @ dbfiddle here

---

reference:

• postgresql json functions , operators
• cast