How to select each value of array

Question

Consider following case

Table : tab1
id    serial      primary key
arr   int[]

Now I want to select each value of arr.

SELECT * FROM (SELECT arr FROM tab1) AS tab2

I need kind of iteration in array.

e.g.

id    arr
-----------------------------
1     [1,2]
2     [5,6,8]

So I could get result as

arr      val
-------------------------------
[1,2]    1
[1,2]    2
[5,6,8]  5
[5,6,8]  6
[5,6,8]  8

Dzmitry Savinkou · Accepted Answer · 2015-12-09 07:04:11Z

1

Use unnest() for that:

WITH array_data(id,arr) AS ( VALUES
  (1,ARRAY[1,2]),
  (2,ARRAY[5,6,8])
)
SELECT arr,unnest(arr) AS val 
FROM array_data;

answered Dec 9, 2015 at 7:04

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

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

Comments

Luca Marletta · Accepted Answer · 2015-12-09 07:09:37Z

0

I don't know if I've got well but here you have all you need

select id,
       unnest(arr),
       array_to_string(arr,','),
       array_length(arr, 1)
from array_data;

answered Dec 9, 2015 at 7:09

Luca Marletta

4575 silver badges14 bronze badges