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 :)

Answer 1

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.