json - 获取 JSONB 属性的计数

Question

我有 jsonb 列answers，它可能看起来像这样，有 3 行：

{answer1: {label: "", value: "answer1_value1"}, answer2: {label: "", value: "answer2_value1"}}
{answer1: {label: "", value: "answer1_value2"}, answer2: {label: "", value: "answer2_value1"}}
{answer1: {label: "", value: "answer1_value2"}, answer2: {label: "", value: "answer2_value2"}}

一组行的答案属性名称可以不同。我想从上面的示例中得到这个结果（按值分组的每个答案的计数）：

answer1: { answer1_value1: 1, answer1_value2: 2}
answer2: { answer2_value1: 2, answer2_value2: 1}

是否可以？

我得到了这样的字段名称SELECT DISTINCT json_object_keys(answers::json) as field，但不知道下一步该怎么做。

标签： jsonpostgresqlaggregate-functionsjsonb

Answer 1

用于jsonb_each()计算答案并将结果聚合回 json 对象：

select jsonb_build_object(key, jsonb_object_agg(val, count))
from (
    select key, value->>'value' as val, count(*)
    from my_table
    cross join jsonb_each(answers) 
    group by key, val
    ) s
group by key;

rextester 中的工作示例。