How to aggregate rows of jsonb elements in postgres?

Question

I want to know if there is any efficient way to aggregate several rows of a JSONB column, consider this table structure:

PERFORMANCE(user_id INTEGER, stat_date DATE, metrics JSONB)

With rows like this:

1, 2017-01-01, {"speed":10, "distance":120, "time":5}
1, 2017-01-02, {"speed":15, "distance":150, "time":8}
1, 2017-01-03, {"speed":9, "distance":90}
2, 2017-01-01, {"speed":15, "distance":150, "time":8}

I would like to aggregate by SUM each key inside "metrics" column by user_id, so the output looks like this:

1, {"speed":34, "distance":360, "time":13}
2, {"speed":15, "distance":150, "time":8}

One option is to use jsonb_each but that will explode the column and I was wondering if there is a better option.

EDIT: It's important to note that I don't know every key inside the JSONB column, so I can't explicitly aggregate them.

Thanks

Roman Tkachuk · Accepted Answer · 2017-05-26 08:48:08Z

14

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
) tt
group by user_id

edited May 26, 2017 at 8:48

Roman Tkachuk

3,2761 gold badge18 silver badges16 bronze badges

answered May 25, 2017 at 17:21

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

crorella Over a year ago

That's what I suspected, but I don't know all the possible values present inside the JSONB column.

Oto Shavadze Over a year ago

What do you mean? Sorry I don't understand this, can you explain ?

Oto Shavadze Over a year ago

Do you mean you don't know all the possible keys inside the JSONB?

crorella Over a year ago

Yes, that's correct. I don't know all the possible keys inside the JSONB.

Oto Shavadze Over a year ago

May be nothing new for you in this case, but I edited my answer, please see it.

Collectives™ on Stack Overflow

How to aggregate rows of jsonb elements in postgres?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related