CODEWITHSUNDEEP
sqlpresto
Edamame
Edamame

Reputation: 25366

Presto query: UNNEST a column which is of ARRAY type

I have the following Presto query:

select team from my_table

The output is:

team
-------
[John, Amy]
[David, Mary, Alex]
[Josh, Ann]

Then I want to see all the people like:

person
----------
John
Amy
David
Mary
Alex
Josh
Ann

I try to use UNNEST like:

select UNNEST(team) from my_table

But got the following syntax error:

mismatched input 'unnest'. Expecting: '*', 'ALL', 'DISTINCT', <expression>, <identifier>

Any idea what I did wrong? Thanks!

Upvotes: 2

Views: 3225

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I think the syntax you want is:

select t.team
from my_table cross join
     unnest(team) as t(team)

Upvotes: 6

Related Questions