How to unnest two or more postgres arrays from a CTE

Question

From the array-functions docs:

unnest ( anyarray, anyarray [, ... ] ) → setof anyelement, anyelement [, ... ]

Expands multiple arrays (possibly of different data types) into a set of rows. If the arrays are not all the same length then the shorter ones are padded with NULLs. This form is only allowed in a query's FROM clause;

Is it possible to use UNNEST with more than one array, when the arrays come from some table, and are not input? All the examples I've found pass the arrays as input.

Given my_table

| type | foo | bar |
+------+-----+-----+
| a    |  1  |  2  |
| a    |  3  |  4  |
| b    |  5  |  6  |
| b    |  7  |  8  |

How would I unnest two arrays, aggregated from the columns foo and bar?

Something like

WITH cte AS (
  SELECT type, array_agg(foo) AS foos, array_agg(bar) AS bars
  FROM my_table
  GROUP BY type
)
SELECT cte.type, t.* FROM UNNEST(cte.foos, cte.bars) AS t(foos, bars)

But this fails with ERROR: missing FROM-clause entry for table cte.

Is this possible? I know I can do it without UNNEST and simply return type in the CTE and then JOIN again on my_table.type to get all the rows with matching type, but in my real world query this is expensive and I'm trying to avoid repeating the scan.

Answer 1

Try using a lateral join like this:

WITH cte AS (
  SELECT type, array_agg(foo) AS foos, array_agg(bar) AS bars
  FROM the_table group by type
)
SELECT cte.type, t.* 
FROM cte
cross join lateral unnest(cte.foos, cte.bars) as t(foos, bars);

Basically this aggregates foo and bar into arrays and then flattens them back into their original structure. BTW group by was missing.

See BD Fiddle

Answer 2

You can call multiple unnest:

WITH cte AS (
  SELECT type, array_agg(foo) AS foos, array_agg(bar) AS bars
  FROM the_table group by type
)
SELECT cte.type, unnest(cte.foos), unnest(cte.bars)
FROM cte;

And this post is relevant for understanding the behavior of multiple set returning functions in a select.