I'm working with postgres and I need to sum the value of jsonb fields
Column │ Type │ Modifiers
─────────────────┼──────────────────────┼────────────────────────────────────────────────────────────────────
id │ integer │ not null default
practice_id │ character varying(6) │ not null
date │ date │ not null
pct_id │ character varying(3) │
astro_pu_items │ double precision │ not null
astro_pu_cost │ double precision │ not null
total │ jsonb │
So I need to sum all the values for the total field for all the records and after that I need to use aggregate functions on them like min, max and avg
I started a query like this
select id, json_object_agg(key, val)
from (
select id, key, sum(value::numeric) val
from mytable t, jsonb_each_text(total)
group by id, key
) s
group by id;
I'm confused after this. Is there a better way to solve this problem?
I need to use the aggregate function after adding all the values inside jsonb. Is that possible?
I'm getting a result like this
id json_object_agg
25 { "a" : 1, "b" : 2 }
26 { "a" : 1, "b" : 2 }
24 { "b" : 2, "a" : 1 }
I was expecting this
id json_object_agg
25 3
26 3
Something like this
CodePudding user response:
To sum all the values of the total
column for each of the rows, you need to group by id
but not by key
:
select t.id, sum(tt.value::numeric) val
from mytable t, jsonb_each(total) AS tt
where jsonb_typeof(tt) = 'number' -- to avoid errors in the sum() if non numeric values
group by t.id
To calculate the min, max, avg on all the rows of the table, you don't need to group the rows :
select min(s.val), max(s.val), avg(s.val)
from
(
select t.id, sum(tt.value::numeric) val
from mytable t, jsonb_each(total) AS tt
where jsonb_typeof(tt) = 'number' -- to avoid errors in the sum() if non numeric values
group by t.id
) as s