PostgreSQL how to merge jsonb keys and count values

Question

I have a table that looks somthing like the following:

id	name	category	specs (jsonb)
1	product1	phones	{ "brand": "brand1", "color": "red", "size": 5, "memory": "8GB"}
2	product2	phones	{ "brand": "brand1", "color": "white", "size": 7, "memory": "8GB"}
3	product3	laptops	{ "brand": "brand20", "storage": "SSD", "os": "os1" , "memory": "32GB"}

My desired output given a specific category

{
    "brand": {
       "brand1": 1,
       "brand2": 1
    },
    "color": {
       "red": 1,
       "white": 5,
    },
    "memory": {
        "8gb": 2,
    }
}

Answer 1

Blow out the specs column with jsonb_each_text(), calculate the counts, and then reassemble with jsonb_object_agg() (the first two CTEs could be combined into one, but I left them verbose for illustration):

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<>fiddle here