How to break a row into multiple rows based on a column value in Athena (Presto)?

Question

I have a Athena table that has a column containing array of values. I want to create multiple rows from one row such that the column of array can be changed to contain only 1 value.

E.g. :

Name   Id   PhoneNumber
Josh   123  [1236348475,5323794875]

to look like :

Name   Id   PhoneNumber
Josh   123  1236348475
Josh   123  5323794875

How can I write my query to achieve this?

Answer 1

I think unnest() does what you want:

select t.name, t.id, u.phonenumber
from t cross join
     unnest(t.phonenumber) u(phonenumber);