I have few records with a field "data" that contains a jsonb with cryptocurrencies amounts:
(cryptocurrencies can be different and in any quantity)
The task is to calculate the total amount of cryptocurrency and get something like:
CODE: SUM:
BTC 185.00002100
DOGE 2.00000000
How can I do this with SQL?
Use jsonb_each_text in a CROSS JOIN to extract each element of your json records, after that sum the values grouping by key
SELECT j.key, sum(j.value::numeric)
FROM mytable
CROSS JOIN jsonb_each_text(data::jsonb) j (key,value)
GROUP BY j.key
Demo: db<>fiddle
A natural approach is to get the sums in a single row:
with my_table(data) as (
values
('{"BTC": "0.1"}'::jsonb),
('{"BTC": "0.2", "DOGE": "2.0"}'),
('{"BTC": "0.3"}')
)
select
sum((data->>'BTC')::numeric) as btc,
sum((data->>'DOGE')::numeric) as doge
from my_table;
btc | doge
-----+------
0.6 | 2.0
(1 row)
If you insist on a columnar presentation:
with my_table(data) as (
values
('{"BTC": "0.1"}'::jsonb),
('{"BTC": "0.2", "DOGE": "2.0"}'),
('{"BTC": "0.3"}')
)
select code, sum((data->>code)::numeric)
from my_table
cross join unnest(array['BTC', 'DOGE']) as code
group by code
order by code;
code | sum
------+-----
BTC | 0.6
DOGE | 2.0
(2 rows)
