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

Since LEFT JOIN UNNEST doesn't work on Athena Presto, you can cross-join on null values using the IF and CARDINALITY like this:

WITH example AS (
  SELECT devop, devs 
  FROM UNNEST(ARRAY['Sharon', 'John', 'Bob', 'Sally']) AS t(devop) CROSS JOIN
       UNNEST(IF(CARDINALITY(ARRAY[])=0, ARRAY[NULL], ARRAY[]) AS t(devs)
)

This way, if the ARRAY[]/Column is empty (CARDINALITY checks the size of array), ARRAY[NULL] is returned and the row is not skipped.

(tested on Presto 0.217 / Athena engine version 2)

Answer 2

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

Answer 3

I don't think that you want a cross join here. Instead, you could phrase this as:

select 
    array_join(
        array_agg(distinct unnest(array['Sharon', 'John', 'Bob', 'Sally'])),
        ';'
    ) devops,
    array_join(
        array_agg(distinct unnest(array[])),
        ';'
    ) devs

Answer 4

Try to array_union with a null array,

Below query will give you the desired result. Tested on Athena engine version 1.

WITH example AS (
 SELECT devop, devs 
 FROM

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