SQL presto - cross join unnest null value

Question

I have arrays of different sizes and I want each value in the array to be in separate rows. To do that, I have used the cross join unnest. It is working however, it is deleting null array.

So, I have my column ID with the different arrays and some are nulls, when I do

select *
    from table 
    cross join unnest (t.id) as t(order_id)
    where length(order_id) = 5  or order_id is NULL

I only get the following results

ID	order_id
23deo jfr32 6582w	23deo
23deo jfr32 6582w	jfr32
23deo jfr32 6582w	6582w

and I want

ID	order_id
23deo jfr32 6582w	23deo
23deo jfr32 6582w	jfr32
23deo jfr32 6582w	6582w
null	null

If someone knows how to unnest null values it would be much appreciated. I've been looking on the internet and I saw that we could include a WITH ORDINALITY clause but I don't know how it works.

score 5 · Answer 1 · answered May 06 '21 at 16:06

Use LEFT JOIN UNNEST instead of CROSS JOIN UNNEST. Feature was added in Presto 319

If you have previous version, then the workaround can be using subquery with LEFT JOIN:

with exploded as (
select *
    from table t
    cross join unnest (t.id) as t(order_id)
)

select t.*, e.order_id 
  from table t
       left join exploded e on t.join_key=e.join_key

Just use correct join key

SQL presto - cross join unnest null value

1 Answers1