# How does unnest() treat NULL values and empty arrays?

I am trying to achieve something like `LEFT JOIN` with array `unnest()` function. I want the query to return a row with a null value if the array is empty. Therefore, by using a `CASE` construct, I wanted to pass fake array with single null element if the source array is empty, but it does not work as expected:

Query 1

``````select element
from (
select array['a']::text[] as arr --< single non-null element
) sub, unnest(
(
case when array_length(sub.arr, 1) <= 0 then (array[null])::text[]
else sub.arr
end
)
) element
-- returns 1 row with element = "a"
``````

Query 2

``````select element
from (
select array[]::text[] as arr --< empty array
) sub, unnest(
(
case when array_length(sub.arr, 1) <= 0 then (array[null])::text[]
else sub.arr
end
)
) element
-- returns 0 rows (should return 1 row with element = NULL?)
``````

Query 3

``````select element
from (
select array[null]::text[] as arr --< single null element
) sub, unnest(
(
case when array_length(sub.arr, 1) <= 0 then (array[null])::text[]
else sub.arr
end
)
) element
-- return single row with element = NULL
``````

Just figured out that `select array_length(array[]::text[], 1)` returns `NULL` - my question is why?

The thing is that that syntax creates an array of zero dimensions, not one that has 1 dimension and zero elements. So "0" would be incorrect.

Hard to say whether `array_length()` should return `NULL` or `0` for an empty array dimension ...

### Solution

You could invert the logic and process the original source array only if the length is `>= 1`:

``````WITH tbl(id, arr) AS (
VALUES
(1, '{a}'::text[])
, (2, '{NULL}'::text[])
, (3, '{}'::text[])
, (4, '{x,y,z}'::text[])
)
SELECT id, elem
FROM   tbl t
, unnest (
CASE WHEN array_length(t.arr, 1) >= 1
THEN t.arr
ELSE '{null}'::text[] END
) elem;
``````

Returns at least one row for each source row.

Or, much simpler, use an actual `LEFT JOIN` (the comma in your `FROM` clause is short notation for `CROSS JOIN`):

``````SELECT id, elem
FROM   tbl t
LEFT   JOIN LATERAL unnest(t.arr) elem ON true;
``````

This fills in NULL values automatically for columns of the right table if no match is found. Exactly what you are trying to achieve.
Related:

db<>fiddle here