SQL/Presto expanding a list column into multiple rows

Question

Given a hive table like

a  |   b
----------
1  |  [1,2,3]
2  |  [2,3]

How can I create a secondary table like

a | b
------
1 | 1
1 | 2 
1 | 3 
2 | 2 
2 | 3 

Answer 1

This is a textbook case for unnest:

-- sample data
WITH dataset (a, b) AS (
    VALUES (1, array[1,2,3]),
    (2, array[2,3])
) 

-- query
select a, bb
from dataset
cross join unnest(b) as t(bb)
order by a, bb -- to pretify output

Output:

a	bb
1	1
1	2
1	3
2	2
2	3