Flattening a relation with an array to emit one row per array entry

Question

Given a table defined as such:

CREATE TABLE test_values(name TEXT, values INTEGER[]);

...and the following values:

| name  | values  |
+-------+---------+
| hello | {1,2,3} |
| world | {4,5,6} |

I'm trying to find a query which will return:

| name  | value |
+-------+-------+
| hello | 1     |
| hello | 2     |
| hello | 3     |
| world | 4     |
| world | 5     |
| world | 6     |

I've reviewed the upstream documentation on accessing arrays, and tried to think about what a solution using the unnest() function would look like, but have been coming up empty.

An ideal solution would be easy to use even in cases where there were a significant number of columns other than the array being expanded and no primary key. Handling a case with more than one array is not important.

Doesn't select name, unnest(values) "value" from test_values; work? — bgoldst
– bgoldst, Commented Aug 13, 2015 at 20:22

Erwin Brandstetter · Accepted Answer · 2021-12-17 17:32:10Z

33

We can put the set-returning function unnest() into the SELECT list like Raphaël suggests. This used to exhibit corner case problems before Postgres 10. See:

What is the expected behaviour for multiple set-returning functions in SELECT clause?

Since Postgres 9.3 we can also use a LATERAL join for this. It is the cleaner, standard-compliant way to put set-returning functions into the FROM list, not into the SELECT list:

SELECT name, value
FROM   tbl, unnest(values) value;  -- implicit CROSS JOIN LATERAL

One subtle difference: this drops rows with empty / NULL values from the result since unnest() returns no row, while the same is converted to a NULL value in the FROM list and returned anyway. The 100 % equivalent query is:

SELECT t.name, v.value
FROM   tbl t
LEFT   JOIN unnest(t.values) v(value) ON true;

See:

What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

edited Dec 17, 2021 at 17:32

answered Aug 14, 2015 at 2:45

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

George Pipis Over a year ago

Thank you for the nice answer. A quick question. What is the v(value) on the last query. I understood that v is the alias, but I have never seen this notation in the past. Many thanks again for your answer

Raphaël Althaus · Accepted Answer · 2015-08-13 20:22:35Z

14

Well, you give the data, the doc, so... let's mix it ;)

select 
 name, 
 unnest(values) as value 
from test_values

see SqlFiddle

answered Aug 13, 2015 at 20:22

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

1 Comment

Charles Duffy Over a year ago

Unfamiliar with the semantics of selecting a tuple with a sequence as one of its elements, I can plead. I'll accept this when the timer runs out.

Collectives™ on Stack Overflow

Flattening a relation with an array to emit one row per array entry

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related