How to aggregate rows of jsonb elements in postgres?

Viewed 6444

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

1 Answers
Related