通过组的复杂JSON B聚集

发布于 2025-01-24 14:09:27 字数 574 浏览 2 评论 0原文

我有一个表格的表格，

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 我知道如何使其与硬编码一起使用。但是我的问题是钥匙的NO可以是6-9。我无法将SQL中的密钥编码。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

遇见了你 2025-01-31 14:09:27

您可以使用这样的功能使用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

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

回复收藏 0 原文

~没有更多了~