SQL: CROSS JOIN UNNEST and include data from rows with NULLs in CROSS JOIN UNNEST column

Question

I'm looking for assistance in the below SQL query where column policy_array has NULL values in some rows, but arrays of policy data in others. I would like to be able to include data from rows even when policy_array is NULL in the output.

When I execute the below query it executes a CROSS JOIN UNNEST as expected but also drops all data from columns with NULLs in the column policy_array as expected as well. I can imagine a work around by having an intermediate table where NULLs in policy_array are changed to something else, but I really would prefer not to do that.

 SELECT 
    policy,
    account_id,
    rejects,
    overturns,
    appeals,
    submits
FROM relevant_table
CROSS JOIN UNNEST(policy_array) AS p (policy)
WHERE
    ...

Answer 1

There are two options either LEFT JOIN with on true:

FROM relevant_table
LEFT JOIN UNNEST(policy_array) AS p (policy) ON true

Or a little bit more hackish which uses the fact that unnest supports multiple arrays - add array with one element (also note succinct syntax for cross join unnest):

FROM relevant_table, 
    UNNEST(policy_array, array[1]) AS p (policy, ignored)