Complex JSON B aggregation with GROUP BY-CodePudding

I have a table something like this

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}

Expected

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

I want to group by and sum up the value within the JSON. I tried using JSONB_OBJ_AGG I know how to get it to work with hardcoding. But my problem is the no of keys can be 6-9. I cannot hardcode the keys in my SQL.

CodePudding user response：

You can use the jsonb_object_agg function like this to get the sum of all keys without declaring them:

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

Demo in DBfiddle