How to concatenate strings of jsonb elements in postgres?
I want to know if there is an efficient way to aggregate multiple rows a JSONB column, consider this table structure:
PERFORMANCE(user_id INTEGER, stat_date DATE, metrics JSONB)
With lines 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 SUM aggregate each key inside the user_id "metric" column, 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 cause the column to explode and I was wondering if there is a better option.
EDIT: It's important to note that I don't know every key inside a JSONB column, so I can't explicitly fill them in.
thank
source to share
One way is to just get the values from the jsonb data, sum them using group by user_id
, and then execute the 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 that as you said, you can use the function jsonb_each
(maybe there are better ways, but I don't see it now). I'm using jsonb_each_text
here, I don't know what you mean but that will explode the column
, you can use jsonb_object_agg
to "create" the 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
source to share