如何使用SQL来展开包含多列数组的表

Question

如何用num和letter列中的数组转换此表：

id  |    num    |  letter
-----+-----------+-----------
111 | [1, 2]    | [a, b]
111 | [3, 4]    | [c, d]
222 | [5, 6, 7] | [e, f, g]

进入该表

id  | num | letter
-----+-----+--------
111 |   1 | a
111 |   2 | b
111 |   3 | c
111 |   4 | d
222 |   5 | e
222 |   6 | f
222 |   7 | g

附件：这里有一些sql可以用来尝试执行转换

with test as(
select * from (
values
(111, array[1,2], array['a','b']),
(111, array[3,4], array['c','d']),
(222, array[5,6,7], array['e','f', 'g'])
) as t (id, num, letter)
)
select
*
from test

Answer 1

PrestoDB似乎支持具有多个参数的unnest()：

select t.id, u.n, u.l
from test cross join
unnest(num, letter) as u(n, l)