I'm using Trino/Presto and trying to unnest array column which can contain rows with empty or null arrays which results in such rows missing:
with table1(id, arr) as (
values (1, array[1,2,3]),
(2, array[]),
(3, array[42]),
(4, null)
)
select id, a
from table1
cross join unnest(arr) as t(a);
And output:
id | a
----+----
1 | 1
1 | 2
1 | 3
3 | 42
As you see ids 2 and 4 are missing. Is it possible to rewrite query so they will be present?
unnest
allows specifying multiple arrays to unnest, when they have different cardinality the "missing" values will be filled with null
s, so you can use this to work around (note the succinct syntax for unnest
allowing to skip the cross join
):
-- query
select id, a
from table1,
unnest(arr, array[1]) as t(a, ignored);
or using left join:
-- query
select id, a
from table1
left join unnest(arr) as t(a) on true;