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?

>Solution :

unnest allows specifying multiple arrays to unnest, when they have different cardinality the "missing" values will be filled with nulls, 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);