Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE results (n text, a int[], q int);
INSERT INTO results VALUES
  ('foo', '{1,2}'::int[], 1)
, ('foo', '{3,4,5}', 1)
;

CREATE TABLE



INSERT 0 2


-- create custom aggregate func for simple concatenation ONCE
CREATE AGGREGATE array_concat (anycompatiblearray) (
  sfunc = array_cat
, stype = anycompatiblearray
, initcond = '{}'
);

SELECT n, array_concat(a) AS a, sum(q) AS q
FROM   results
GROUP  BY n;

CREATE AGGREGATE




n
a
q


foo
{1,2,3,4,5}
2



SELECT 1


-- Oliver's workaround -- for exactly 2 dimensions in the intermediarry aggregate
SELECT n, ARRAY(SELECT JSONB_ARRAY_ELEMENTS(JSONB_ARRAY_ELEMENTS(JSONB_AGG(a)))) AS a, SUM(q) AS q
FROM   results
GROUP  BY n;


n
a
q


foo
{1,2,3,4,5}
2



SELECT 1


-- workaround with jsonb  -- for up to 2 dimensions in the intermediarry aggregate
SELECT n
     , ARRAY(SELECT jsonb_path_query(jsonb_agg(a), '$[*][*]')::int) AS a
     , sum(q) AS q
FROM   results
GROUP  BY n;


n
a
q


foo
{1,2,3,4,5}
2



SELECT 1


-- NOTE the effect on multi-dimensional arrays!
INSERT INTO results VALUES
  ('bar', '{{6,7},{8,9}}', 2)      -- 2-dim
, ('bar', '{{10,11},{12,13}}', 3)  -- 2-dim
;

SELECT n, array_concat(a) AS a, sum(q) AS q
FROM   results
GROUP  BY n
ORDER  BY n;

INSERT 0 2




n
a
q


bar
{{6,7},{8,9},{10,11},{12,13}}
5

foo
{1,2,3,4,5}
2



SELECT 2


-- Add not-matching array dimesions
INSERT INTO results VALUES
  ('baz', '{{66,67},{68,69}}', 4)  -- 2-dim
, ('baz', '{0}'              , 5)  -- 1-dim
;

INSERT 0 2


-- custom aggregate FAILS now!
SELECT n, array_concat(a) AS a, sum(q) AS q
FROM   results
GROUP  BY n
ORDER  BY n;

ERROR:  cannot concatenate incompatible arrays
DETAIL:  Arrays with differing dimensions are not compatible for concatenation.


-- Oliver's workaround FAILS for > 2 dims!
SELECT n, ARRAY(SELECT JSONB_ARRAY_ELEMENTS(JSONB_ARRAY_ELEMENTS(JSONB_AGG(a)))) AS a, SUM(q) AS q
FROM   results
GROUP  BY n;


n
a
q


bar
{"[6, 7]","[8, 9]","[10, 11]","[12, 13]"}
5

foo
{1,2,3,4,5}
2

baz
{"[66, 67]","[68, 69]",0}
9



SELECT 3


-- my jsonb workaround FAILS for > 2 dims!
SELECT n
     , ARRAY(SELECT jsonb_path_query(jsonb_agg(a), '$[*][*]')::int) AS a
     , sum(q) AS q
FROM   results
GROUP  BY n;

ERROR:  cannot cast jsonb array to type integer


-- extend Oliver's jsonb workaround to 3 dims
SELECT n, ARRAY(SELECT jsonb_array_elements(jsonb_array_elements(jsonb_array_elements(jsonb_agg(a))))) AS a, SUM(q) AS q
FROM   (
   VALUES
     ('bar', '{{6,7},{8,9}}'::int[], 2)  -- 2-dim
   , ('bar', '{{10,11},{12,13}}'   , 3)  -- 2-dim
   ) results (n,a,q)
GROUP  BY n;


n
a
q


bar
{6,7,8,9,10,11,12,13}
5



SELECT 1


-- but that fails for anything else:
SELECT n, ARRAY(SELECT jsonb_array_elements(jsonb_array_elements(jsonb_array_elements(jsonb_agg(a))))) AS a, SUM(q) AS q
FROM   results
GROUP  BY n;

ERROR:  cannot extract elements from a scalar


-- extend my jsonb workaround to 1-3 dims
SELECT n
     , ARRAY(SELECT jsonb_path_query(jsonb_agg(a), '$[*][*][*]')::int) AS a
     , sum(q) AS q
FROM   results
GROUP  BY n
ORDER  BY n;


n
a
q


bar
{6,7,8,9,10,11,12,13}
5

baz
{66,67,68,69,0}
9

foo
{1,2,3,4,5}
2



SELECT 3


-- workaround with plain SQL
SELECT n, a, q
FROM (
   SELECT n, sum(q) AS q
   FROM   results
   GROUP  BY n
   ) q
FULL JOIN (
   SELECT n, array_agg(elem) AS a
   FROM   results, unnest(a) AS elem
   GROUP  BY n
   ) a USING (n)
ORDER  BY n;


n
a
q


bar
{6,7,8,9,10,11,12,13}
5

baz
{66,67,68,69,0}
9

foo
{1,2,3,4,5}
2



SELECT 3
n	a	q
bar	{"[6, 7]","[8, 9]","[10, 11]","[12, 13]"}	5
foo	{1,2,3,4,5}	2
baz	{"[66, 67]","[68, 69]",0}	9