PostgreSQL：对比两个数组获取不匹配元素并保留原顺序

问题分析 #

使用EXCEPT操作符对比两个数组的元素时，结果会自动去重且不保留原数组的顺序，比如执行：

array(select
         unnest(
          array['12', '34', '56', 'NULL'])
           except select unnest(
            array['AA', 'cc', 'P4', 'G8']
        )) as modifiers

得到的结果是[NULL,56,12,34]，顺序完全打乱。我们需要从第一个数组中筛选出不在第二个数组里的元素，同时严格保留第一个数组的原有顺序。

解决方案 #

通过unnest WITH ORDINALITY获取原数组元素的位置索引，过滤掉存在于第二个数组的元素后，按索引排序再重新聚合为数组：

基础示例SQL

SELECT array_agg(val ORDER BY idx) AS modifiers
FROM unnest(array['12', '34', '56', 'NULL']) WITH ORDINALITY AS t(val, idx)
WHERE val NOT IN (SELECT unnest(array['AA', 'cc', 'P4', '34']));

执行后会得到期望结果：['12', '56', 'NULL']。

处理包含NULL的场景

如果第二个数组中有NULL值，NOT IN会出现逻辑问题（因为NULL NOT IN (...)的结果为未知），此时改用NOT EXISTS：

SELECT array_agg(t.val ORDER BY t.idx) AS modifiers
FROM unnest(array['12', '34', '56', 'NULL']) WITH ORDINALITY AS t(val, idx)
WHERE NOT EXISTS (
    SELECT 1
    FROM unnest(array['AA', 'cc', 'P4', 'NULL']) AS s(val)
    WHERE t.val = s.val
);

原理说明 #

1. unnest WITH ORDINALITY：展开数组的同时为每个元素生成递增的位置索引，记录元素在原数组中的顺序。
2. 过滤逻辑：通过NOT IN或NOT EXISTS筛选出第一个数组中不存在于第二个数组的元素。
3. array_agg(val ORDER BY idx)：按原数组的索引顺序重新聚合元素，确保结果数组的顺序和原数组完全一致。

内容的提问来源于stack exchange，提问作者CAK