Aggregate jsonb map with string array as value in postgresql

Question

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"]}

Ajax1234 · Accepted Answer · 2023-02-09 04:59:17Z

1

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.

answered Feb 9, 2023 at 4:59

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Santino Agosti · Accepted Answer · 2023-02-09 04:17:34Z

0

You can use the jsonb_object_agg aggregate function to achieve this. The jsonb_object_agg function takes a set of key-value pairs and returns a JSONB object. You can use this function to aggregate all the maps into a single JSONB map by concatenating all the maps as key-value pairs. Here is an example query:

SELECT jsonb_object_agg(key, value)
FROM (
  SELECT key, jsonb_agg(value) AS value
  FROM (
    SELECT key, value
    FROM (
      SELECT 'a' AS key, '["1", "2"]'::jsonb AS value
    UNION ALL
      SELECT 'a' AS key, '["3"]'::jsonb AS value
    UNION ALL
      SELECT 'b' AS key, '["5"]'::jsonb AS value
    ) subq
  ) subq2
  GROUP BY key
) subq3;

This will give you the following result:

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

edited Feb 9, 2023 at 4:17

answered Feb 9, 2023 at 4:17

Santino Agosti

12 bronze badges

1 Comment

Abhay Gupta Over a year ago

Suppose I have a table named 'Objects' and a column named 'Tags'. How can I use above approach on the table? Do I have to union all the rows?

Collectives™ on Stack Overflow

Aggregate jsonb map with string array as value in postgresql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related