PostgreSQL-按jsonb列分组 [英] PostgreSQL - grouping by jsonb column
本文介绍了PostgreSQL-按jsonb列分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有survey_results
表,该表具有以下列:
I have survey_results
table which has following columns:
id - integer
score_labels - jsonb
score_labels
列数据格式如下:
{"total": "High", "risk": "High"}
{"total": "High", "risk": "High"}
现在,我想拥有一个SQL查询,该查询将通过此score_labels
列对我的调查结果进行分组和计数.最终结果应该是这样:
Now I want to have sql query that will group and count my survey results by this score_labels
column. This is what the final result should look like:
total risk
------- ------
{high: 2, medium: 1, low: 0} {high: 1, medium: 2, low: 1}
我想通过得分标签来统计调查结果.有没有办法在PostgreSQL中做到这一点?
I want to count survey results by its score labels. Is there way to do it in PostgreSQL?
这是具有以下架构的简单sqlfiddle:
Here is simple sqlfiddle with the following schema:
http://sqlfiddle.com/#!17/0367f/1/0
推荐答案
一种有点复杂的聚合:
with my_table (id, score_labels) as (
values
(1, '{"total": "High", "risk": "High"}'::jsonb),
(2, '{"total": "High", "risk": "Low"}'::jsonb),
(3, '{"total": "Low", "risk": "Medium"}'::jsonb)
)
select
jsonb_build_object(
'high', count(*) filter (where total = 'High'),
'medium', count(*) filter (where total = 'Medium'),
'low', count(*) filter (where total = 'Low')
) as total,
jsonb_build_object(
'high', count(*) filter (where risk = 'High'),
'medium', count(*) filter (where risk = 'Medium'),
'low', count(*) filter (where risk = 'Low')
) as risk
from (
select
score_labels->>'total' as total,
score_labels->>'risk' as risk
from my_table
) s
total | risk
------------------------------------+------------------------------------
{"low": 1, "high": 2, "medium": 0} | {"low": 1, "high": 1, "medium": 1}
(1 row)
这篇关于PostgreSQL-按jsonb列分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文