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

Postgresql – How to unnest two or more postgres arrays from a CTE

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
)
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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

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

Back To Top
Search