Make multiple JSONArray rows into one single row by grouping with some other column in postgresql

user_7940365 0
Viewed 328

I have a view from a query select * from table which returns the below data

enter image description here

I want to group by the name column which have same name and merge the JSONArray column like mentioned below

enter image description here

aggregate-functions postgresql
1 Answers

One way to do this, is to unnest the arrays and then aggregate them back:

select t.id, t.name, jsonb_agg(a.e)
from the_table t
  cross join lateral jsonb_array_elements(t.json_array) as a(e)
group by t.id, t.name;

If you do that a lot, a custom aggregate makes this a bit easier to user (but probably not faster)

create function jsonb_array_combine(p_one jsonb, p_two jsonb)
  returns jsonb
as
$$
  select jsonb_agg(e)
  from (
    select e
    from jsonb_array_elements(p_one) as o(e)
    union all
    select e
    from jsonb_array_elements(p_two) as t(e)
  ) t
$$
language sql 
immutable;

create aggregate jsonb_array_agg(jsonb) 
(
  SFUNC = jsonb_array_combine(jsonb, jsonb),
  STYPE = jsonb
);

Then you can use it like this:

select t.id, t.name, jsonb_array_agg(t.json_array)
from the_table t
group by t.id, t.name;
user_3303150
Related