PostgreSQL-对匹配键上的JSON行求和 [英] Postgresql - Sum JSON rows on matching keys

问题描述

我的数据库中有两列，其中一列uid(myuid)，另一列代表每个用户的总宠物数的键值对(totalpetsjson).我想查询uid，然后对键匹配的JSON行求和.

I have two columns in my database, one of uid (myuid) and another of key value pairs representing total pets per user (totalpetsjson). I would like to query on uid and then sum the resulting JSON rows where keys match.

此查询

SELECT totalpetsjson FROM mytable WHERE "myuid" IN ('john','andy') 

结果分为两行

{'dogs':3,'cats':5,'fish':144}
{'dogs':2,'lizards':4,'cats':3'}

我想要的结果是什么.我如何查询并合并上面的两行，如下所示?

What I want the result to be. How could I query and combine the above two rows to look like below?

{'dogs':5,'cats':8,'fish':144,'lizards':4}

推荐答案

使用功能

Use the function json_each_text() which gives pairs (key, value), cast values to integer and sum them in groups by keys. Finally, aggregate the results to json:

select json_object_agg(key, sum)
from (
    select key, sum(value::int)
    from my_table 
    cross join json_each_text(totalpetsjson)
    where myuid in ('john','andy') 
    group by key
    ) s

                     json_object_agg                     
---------------------------------------------------------
 { "fish" : 144, "lizards" : 4, "cats" : 8, "dogs" : 5 }
(1 row) 

rextester中的实时演示.

这篇关于PostgreSQL-对匹配键上的JSON行求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！