unnesting empty or null array leading to missing rows

Question

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?

Answer 1

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);

or using left join:

-- query
select id, a
from table1
left join unnest(arr) as t(a) on true;

Answer 2

UNNEST of a null value or an empty array produces an empty table. If you want to preserve all the rows from the left side of that join, you need to use a LEFT JOIN instead of a CROSS JOIN, since the latter won't produce any rows when one side is empty. Think of the join operation as a join between each row on the left side with the table produced by calling the UNNEST function on the value of the given column in that row.

WITH table1(id, arr) AS (
    VALUES (1, array[1,2,3]),
           (2, array[]),
           (3, array[42]),
           (4, null)
)
SELECT id, a
FROM table1
LEFT JOIN UNNEST(arr) AS t(a) ON true;

output:

 id |  a
----+------
  1 |    1
  1 |    2
  1 |    3
  2 | NULL
  3 |   42
  4 | NULL
(6 rows)