How to aggregate rows of jsonb elements in postgres? How to aggregate rows of jsonb elements in postgres? sql sql

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