looking for "Sum and combine" json columns-CodePudding

In PostgreSql I can't find in the docs a function that could allow me to combine n json entities, whilst summing the value part in case of existing key/value pair

English not being my main language, I suspect I don't know how to search with the right terms

In other words

from a table with 2 columns

name      data 
'didier'  {'vinyl': 2, 'cd': 3)
'Anne'    {'cd' : 1, 'tape' : 4} 
'Pierre'  {'cd' : 1, 'tape': 9, 'mp3':2}

I want to produce the following result :

{ 'vinyl' : 2, 'cd' : 5, 'tape':13, mp3 : 2}

With is a "combine and sum" function

Thanks in advance for any idea

Didier

CodePudding user response：

Using the_table CTE for illustration, first 'normalize' data column then sum per item type (k) and finally aggregate into a JSONB object.

with the_table("name", data) as 
(
 values
 ('didier',  '{"vinyl": 2, "cd": 3}'::jsonb),
 ('Anne',    '{"cd" : 1, "tape" : 4}'), 
 ('Pierre',  '{"cd" : 1, "tape": 9, "mp3":2}')
)
select jsonb_object_agg(k, v) from 
(
 select lat.k, sum((lat.v)::integer) v 
 from the_table 
 cross join lateral jsonb_each(data) as lat(k, v)
 group by lat.k
) t;

-- {"cd": 5, "mp3": 2, "tape": 13, "vinyl": 2}