CODEWITHSUNDEEP
jsonpostgresqljsonb
vanzhiganov
vanzhiganov

Reputation: 33

Math calculating with two JSONB keys in Postgres?

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;

Upvotes: 3

Views: 1572

Answers (2)

Abelisto
Abelisto

Reputation: 15624

SELECT
    *, j.draw + j.win as total_plays
FROM
    plays_data,
    json_to_record(data->'plays') as j(win int, draw int, lose int);

Upvotes: 2

joanolo
joanolo

Reputation: 6328

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:

  1. -> 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).

  2. ->> 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.

  3. 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:

Upvotes: 7

Related Questions