使用 postgres 選擇過濾後的數組

我希望從包含 ID 和值數組的表中選擇數據。但在查詢結果中，我想過濾數組並刪除與白名單重疊的值。

出於說明目的，以下查詢顯示瞭如何使用單列中的值白名單來格式化數據，然後是我要從中選擇的表…，其中包含唯一 ID 和關鍵字數組。

with whitelist as (
   select distinct unnest(array['a','b','c','f','h']) as keywords
), raw_with_keyword_array as (
   select * from  (
       values 
           ('1234', array['a','c','e','h','q']),
           ('1235', array['a','d','e','f']) 
   ) as t (id, keyword_array)
)
select...

我正在尋找的預期結果是 raw_with_keyword_array 表中的兩行，但過濾後的數組不包括白名單中的值。

我想我可能需要從主表中選擇數據，然後進行某種遞歸子查詢來過濾掉列入白名單的值，並將結果放回一個新數組中？

任何建議，將不勝感激！

例如

第1234章

第1235章

不確定這是否是最有效的方法，但這有效。

您必須取消嵌套所有元素keyword_array才能過濾掉不需要的元素：

with whitelist (kw) as (
   values ('a'),('b'),('c'),('f'),('h')
), raw_with_keyword_array (id, keyword_array) as (
 values 
     ('1234', array['a','c','e','h','q']),
     ('1235', array['a','d','e','f']) 
)
select id, 
      array(select kw
            from unnest(ra.keyword_array) as t(kw)
            except 
            select kw
            from whitelist)
from raw_with_keyword_array ra;

而不是except你也可以使用where not exists：

....
select id, 
      array(select kw
            from unnest(ra.keyword_array) as t(kw)
            where not exists (select *
                              from whitelist wl
                              where wl.kw = t.kw)
            )
from raw_with_keyword_array ra;

我不認為它有很大的不同。

線上範例

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