separate nested columns into rows [sql]

Question

I was trying with cross join and unnest , but I only managed to split one column, not all three at the same time

I have this table in amazon athena

and I want to separate the columns with lists into rows, leaving a table like this

COL1	COL2	COL3	COL4	COL5	COL6
765045	5782	jd938	1	a	pickup
765045	5782	jd938	2	b	delivery
41118	78995	kd982	5	g	pickup
41118	78995	kd982	8	q	delivery
411620	65852	km0899	9	k	pickup
411620	65852	km0899	6	b	delivery

select 
     t.COL1, t.COL2,t.COL3, u.COL4
from t
cross join
     unnest(t.COL4) u(COL4)

I was thinking of making subtables and repeating this code 3 times but I wanted to know if there is a more efficient way

Guru Stron · Answer

unnest supports handling multiple columns in one statement. Also you can use succinct syntax omitting the CROSS JOIN:

select 
     t.COL1, t.COL2,t.COL3, u.COL4, u.COL5, u.COL6
from t,
     unnest(t.COL4, t.COL5, t.COL6) AS u(COL4, COL5, COL6)

Note that for array of different cardinality it will substitute missing values with null's. And if all arrays are empty the row will not be added to the final result (but you can work around this by adding a dummy array with one element like was done here).

separate nested columns into rows [sql]

1 Answers