Search code examples
sqlpostgresqlaggregationjsonb

Aggregate jsonb map with string array as value in postgresql

I have postgresql table with a jsonb column containing maps with strings as keys and string arrays as values. I want to aggregate all the maps into a single jsonb map. There should be no duplicate values in string array. How can I do this in postgres.

Eg:

Input: {"a": ["1", "2"]}, {"a": ["2", "3"], "b": ["5"]}

Output: {"a": ["1", "2", "3"], "b": ["5"]}

I tried '||' operator but it overwrites values if there as same keys in maps.

Eg:

Input: SELECT '{"a": ["1", "2"]}'::jsonb || '{"a": ["3"], "b": ["5"]}'::jsonb;

Output: {"a": ["3"], "b": ["5"]}

Solution

  • Using jsonb_object_agg with a series of cross joins:

    select jsonb_object_agg(t.key, t.a) from (
   select v.key, jsonb_agg(distinct v1.value) a from objects o 
   cross join jsonb_each(o.tags) v 
   cross join jsonb_array_elements(v.value) v1
   group by v.key) t

    See fiddle.