unnest() 如何處理 NULL 值和空數組？

我正在嘗試LEFT JOIN用數組unnest()函式來實現類似的東西。如果數組為空，我希望查詢返回具有空值的行。因此，通過使用CASE構造，如果源數組為空，我想傳遞帶有單個 null 元素的假數組，但它不能按預期工作：

查詢 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"

查詢 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?)

查詢 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

---

剛剛發現select array_length(array[]::text[], 1)返回NULL- 我的問題是為什麼？

在 pgsql-hackers 上的執行緒“Should array_length() Return NULL”中引用Tom Lane ：

問題是該語法創建了一個零維數組，而不是一個具有 1 維和零元素的數組。所以“0”是不正確的。

很難說是否array_length()應該返回NULL或0為空數組維度……

解決方案

僅當長度為 時，您才能反轉邏輯>= 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;

為每個源行返回至少一行。

或者，更簡單的是，使用***實際LEFT JOIN***的（您的子句中的逗號FROM是 的簡寫CROSS JOIN）：

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

如果未找到匹配項，則會自動為右表的列填充 NULL 值。正是你想要達到的目標。

有關的：

• LATERAL 和 PostgreSQL 中的子查詢有什麼區別？

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/119201