PostgreSQL如何合并json键和计数值

Question

我有一个表，看起来像这样:

<表类> id 名称 类别 规格(jsonb) tbody><<tr>1product1手机{"brand"brand1","color":"red","size": 5"memory":"8 gb"}2product2手机{"brand"brand1","color":"white","size": 7,"memory":"8 gb"}3product3笔记本电脑{"brand"brand20","storage":"SSD","os":"os1"，内存"; "32GB"}

用jsonb_each_text()去掉specs列，计算计数，然后用jsonb_object_agg()重新组合(前两个cte可以合并为一个，但为了说明，我留下了它们的冗长):

with blowout as (
select s.category, j.key, j.value
from somthing s
cross join lateral jsonb_each_text(s.specs) as j(key, value)
), counts as (
select category, key, value, count(1) as cnt
from blowout
group by category, key, value
), agg_specs as (
select category, key, jsonb_object_agg(value, cnt) as counts
from counts
group by category, key
)
select category, jsonb_object_agg(key, counts) as output
from agg_specs
group by category
;

db<此处小提琴>