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.
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.
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.
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:
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:
Well, you give the data, the doc, so... let's mix it ;)
select
name,
unnest(values) as value
from test_values
see SqlFiddle
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With