Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 229
Charles Duffy Avatar asked Aug 13 '15 20:08

Charles Duffy


People also ask

What does it mean to flatten a matrix?

Flattening an array is a process of reducing the dimensionality of an array. In other words, it a process of reducing the number of dimensions of an array to a lower number.

What is Unnest in PostgreSQL?

The purpose of unnest function in PostgreSQL is to expand the array into rows. Unnest function generates a table structure of an array in PostgreSQL. Unnest array function is beneficial in PostgreSQL for expanding the array into the set of values or converting the array into the structure of the rows.


2 Answers

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?
like image 155
Erwin Brandstetter Avatar answered Oct 03 '22 01:10

Erwin Brandstetter


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

select 
 name, 
 unnest(values) as value 
from test_values

see SqlFiddle

like image 42
Raphaël Althaus Avatar answered Oct 03 '22 00:10

Raphaël Althaus