CODEWITHSUNDEEP
arraysdatabasepostgresqlunnest
afzalex
afzalex

Reputation: 8652

How to select each value of array

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

Upvotes: 2

Views: 81

Answers (2)

Luca Marletta
Luca Marletta

Reputation: 457

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;

Upvotes: 0

Dzmitry Savinkou
Dzmitry Savinkou

Reputation: 5190

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;

Upvotes: 1

Related Questions