Postgres 9.6 - average on specific field in a jsonb column and group by another field on the same jsonb column

Question

I have the following dataset shown here

http://sqlfiddle.com/#!17/f9280/1

I want to query the data in a way that I get for each key and category the average rank

for instance,

key 1, category 10, avg_rank: 95

key 1, category 20, avg_rank: 75

key 2, category 11, avg_rank: 105

key 2, category 12, avg_rank: 80

Any help or a good reference on how to do that on Postgres will be highly appreciated :)

klin · Accepted Answer · 2018-07-22 12:33:04Z

Use jsonb_array_elements(data) in a lateral join:

select key, value->>'category' as category, avg((value->>'rank')::numeric) as rank from jsonData cross join jsonb_array_elements(data) group by key, category key | category | rank -----+----------+---------------------- 1 | 10 | 95.0000000000000000 1 | 20 | 75.0000000000000000 2 | 11 | 105.0000000000000000 2 | 12 | 80.0000000000000000 (4 rows)

SqlFiddle.

Collectives™ on Stack Overflow

Postgres 9.6 - average on specific field in a jsonb column and group by another field on the same jsonb column

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related