Question 89813 in PostgreSQL Question posted in
The official documentation can be found here.

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

Tags:

2

Answers


  1. 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"]}
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search