Postgres multiply JSONB column with another column

Question

This is my table:

CREATE TABLE my_schema.money
(
    amount numeric NOT NULL,
    rate jsonb
);

and example data:

INSERT INTO my_schema.money (amount, rate)
VALUES (
    15.7, 
    '{
        "date": "2017-06-25",
        "to":
        {
            "USD": 0.282451842549122,
            "GBP": 0.252381738944908
        },
        "from": "EUR"
    }'
);

I'm trying to create a view in the database, with the multiple of the column amount and the rate of USD on column rate, without any success, any ideas?

For example: 15.7 * 0.282451842549122 = 4.4344939280212154.

NotAnAuthor NotAnAuthor · Accepted Answer · 2018-11-16T16:23:14

Assuming your view includes the original columns:

create view my_schema.view_name_for_usd
as
select *, amount * (rate->'to'->>'USD')::numeric usd_amount 
    from my_schema.money

Postgres multiply JSONB column with another column

1 Answers