CODEWITHSUNDEEP
sqlarraysamazon-s3type-conversionamazon-athena
royb
royb

Reputation: 713

aws athena convert array<array<string>> to table

I have an s3 bucket that got a lot of files with this content:

{time:123456, state:{{1,2,3,4},{4,5,6,7}...}

and after I use Athena read it, the result is the dataset with 2 colums: the first one is time int and the second is array<array<string>>, is it possible to convert with athena sql this table to:

select time, col1,col2,col3,col4
from table

when the col1...4 is the names of the columns on the array?

Upvotes: 1

Views: 1394

Answers (1)

leftjoin
leftjoin

Reputation: 38335

Use CROSS JOIN UNNEST to unnest upper array:

select a.time, 
       state_array[1] as col1, 
       state_array[2] as col2,
       state_array[3] as col3,
       state_array[4] as col4
from my_table a
CROSS JOIN UNNEST(state) as t(state_array)

Upvotes: 1

Related Questions