具有组的复杂JSON B聚合

Question

我有一张这样的桌子

STUDENT             JSONB Column 
1                    {"total":8,"healthy": 2,"unhealthy":5,"X":7}                  
1                    {"total":12,"healthy": 4"unhealthy":3,"X":9}    
2                    {"total":3,"healthy": 4}  
2                    {"total":4,"healthy": 1}  

期望的

1                    {"total":20,"healthy": 6,"unhealthy":8,"X":16}    
2                    {"total":7,"healthy": 5}  

我想按JSON中的值进行分组并对其进行汇总。我试着使用JSONB_OBJ_AGG，我知道如何让它与硬编码一起工作。但我的问题是钥匙的编号可以是6-9。我无法对SQL中的键进行硬编码。

Answer 1

您可以像这样使用jsonb_object_agg函数来获得所有键的和，而不需要声明它们：

select id,  jsonb_object_agg(key, sum)  from
(
    select   id, key, sum(value::int)
    from my_table 
    cross join jsonb_each_text(content)
    group by id, key
) tmp_each group by id

DBfiddle中的演示