How to aggregate rows of jsonb elements in postgres?
One way is just get values from jsonb data, sum they using group by user_id
and then make jsonb data again:
select user_id, jsonb_build_object('speed', sum((metrics->>'speed')::numeric), 'distance', sum((metrics->>'distance')::numeric) , 'time', sum((metrics->>'time')::numeric) )from t group by user_id
Edit
Well, in this case I think as you said, you can use jsonb_each
function (May be there is better ways too, but I can't see it now). I use jsonb_each_text
here, I don't know what you mean but that will explode the column
, you can use jsonb_object_agg
for "building" jsonb object again, something like this:
with your_table(user_id , stat_date , metrics) as( select 1, '2017-01-01'::date, '{"speed":10, "distance":120, "time":5}'::jsonb union all select 1, '2017-01-02'::date, '{"speed":15, "distance":150, "time":8}'::jsonb union all select 1, '2017-01-02'::date, '{"bla":8}'::jsonb union all select 4, '2017-01-02'::date, '{"bla":8}'::jsonb union all select 1, '2017-01-03'::date, '{"speed":9, "distance":90}'::jsonb union all select 2, '2017-01-01'::date, '{"speed":15, "distance":150, "time":8}'::jsonb )select user_id, jsonb_object_agg(k, val) from ( select user_id, k, sum(v::numeric) as val from your_table join lateral jsonb_each_text(metrics) j(k,v) on true group by user_id, k) ttgroup by user_id