How can I calc sum for jsonb in PostgreSQL?-CodePudding

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?

CodePudding user response：

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)

CodePudding user response：

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