I have JSONB column with data like:
{"plays": {"win": 90, "draw": 8, "lose": 2}}
How I can calculate sum with win and draw keys?
Something like:
SELECT
data::json#>>'{plays,draw}' + data::json#>>'{plays,win}' as "total_plays",
FROM
plays_data;
Let's assume your table and data are the following ones (note that I have avoided called json any column, to avoid confusion between column names and types; this is a recommended practice):
CREATE TABLE data
(
some_data json
) ;
INSERT INTO data
(some_data)
VALUES
('{"plays": {"win": 90, "draw": 8, "lose": 2}}') ;
You need to use the following query:
SELECT
CAST(some_data->'plays'->>'win' AS INTEGER) + CAST(some_data->'plays'->>'draw' AS INTEGER) AS total_plays
FROM
data ;
| total_plays | | ----------: | | 98 |
Explanation:
-> operator, applied to a JSON column (a JSON object) on the left, and a string on the right, gives back the corresponding field as a JSON object (which might be an object, an array or a value).
->> operator gives back the field value as a text. PostgreSQL doesn't have a way to know whether your data is a string, number or boolean; it treats everything as strings, which is how they're stored.
CAST(expression AS type) converts the expression to the specified type. JavaScript might use a number or a string and cast from one to the other as needed. PostgreSQL must be told explicitly most of the times whether a certain expressions needs to be interpreted as one or the other. As for numbers, JavaScript doesn't let you specify between floats or integers. PostgreSQL needs to be that specific.
You can check everything at dbfiddle here
Reference:
SELECT
*, j.draw + j.win as total_plays
FROM
plays_data,
json_to_record(data->'plays') as j(win int, draw int, lose int);
