Break array_agg column into batches-CodePudding

I have a query like the following:

SELECT mt.group_id, array_agg(mt.id) as my_array
FROM myTable mt
GROUP BY mt.group_id;

In some contexts the array contains too many elements and a max byte limit error is thrown.

Is there any way that I can break the array into separate smaller batches per row? So for example, if I wanted a maximum batch size of 2 the result set might look like:

group_id, my_array
1, {1,2}
1, {3,4}
2, {5}

CodePudding user response：

You can use a window function to add a subgroup column, then group by that column in addition to the existing group_id.

Due to restrictions on where window functions can be used, this requires a nested select to add the subgroup at one level and group by it at a higher level.

with mytable as (select * from (values (1,1),(1,2),(1,3),(1,4),(2,5)) f(group_id,id))

SELECT group_id, array_agg(mt.id) as my_array from 
  (select *,(row_number() over (partition by group_id)-1)/2 as subgroup FROM myTable) mt
GROUP BY mt.group_id, subgroup order by group_id, subgroup;