Return the same row multiple times for each value in a column based on an array

Question

Let's say I have a table with an array column:

id | subIds
1  | {1,2,3}
2  | {4,5}

How can I return a result set:

id | subId    
1  | 1
1  | 2
1  | 3
2  | 4
2  | 5

... in one request without using a function?

+3

SpliFF 15 oct. 14 at 6:02

1 answer

a_horse_with_no_name · Accepted Answer · 2014-10-15T06:09:14+0000

By "without using a function" I assume you mean "without writing my own function to do this".

The function unnest()

will do what you want

select id, unnest(subids) as subid
from the_table;

The order in which elements are returned is undefined.