Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

unnesting empty or null array leading to missing rows

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?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>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);
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading