UNNEST returns no rows for empty array

Question

I am using unnest for more flatten more than one array in Athena query. When the array has some records it returns the correct result. But when the second array is empty it is returning no records. Can someone please let me know how to do unnest to unnest more than one array in a single query?

The following query returns empty row.

WITH example AS (
 SELECT devop, devs 
 FROM

   UNNEST(ARRAY['Sharon', 'John', 'Bob', 'Sally']) AS t(devop),
     UNNEST(ARRAY[]) AS t(devs)
)
select array_join(array_agg(distinct example.devop),';'),array_join(array_agg(distinct example.devs),';') from example

The following query returns the correct result.

WITH example AS (
 SELECT devop, devs 
 FROM

   UNNEST(ARRAY['Sharon', 'John', 'Bob', 'Sally']) AS t(devop),
     UNNEST(ARRAY['a','b']) AS t(devs)
)
select array_join(array_agg(distinct example.devop),';'),array_join(array_agg(distinct example.devs),';') from example

When the second array is empty I want the following result

_col0                             _col1
----------------------------------------------
Sally;John;Bob;Sharon              

Answer 1

Use a left join:

WITH example AS (
      SELECT devop, devs 
      FROM UNNEST(ARRAY['Sharon', 'John', 'Bob', 'Sally']) AS t(devop) LEFT JOIN
           UNNEST(ARRAY[]) AS t(devs)
           ON 1=1
    )
. . .