postgresql - Postgres 9.6 - jsonb 列中特定字段的平均值，并按同一 jsonb 列中的另一个字段分组

Question

我在这里显示了以下数据集

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

我想查询数据的方式是每个键和类别的平均排名

例如，

键 1，类别 10，avg_rank:95

键 1，类别 20，avg_rank:75

键 2，类别 11，av​​g_rank:105

键 2，类别 12，avg_rank:80

任何关于如何在 Postgres 上做到这一点的帮助或好的引用将不胜感激 :)

Answer 1

使用jsonb_array_elements(data)在横向连接中:

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.