Skip to content
Advertisement

SQL/Presto expanding a list column into multiple rows

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

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