PostgreSQL下SQLAlchemy如何查询数组列与Python列表的交集

&&是PostgreSQL的数组重叠判断运算符，仅返回布尔值标识两个数组是否存在公共元素，仅适合做行过滤，无法直接返回交集元素。要实现取交集后仅保留公共元素的需求，可按以下方式实现：

原生PostgreSQL实现 #

核心思路是用unnest将数组拆分为多行元素，通过INTERSECT取公共元素后再重新组装为数组，同时用&&提前过滤无交集的行，该过滤条件可命中数组上的GIN索引，避免无效计算。
针对测试表的SQL写法如下：

SELECT
  id,
  array(
    SELECT unnest(array_column)
    INTERSECT
    SELECT unnest(ARRAY['a', 'b'])
  ) AS array_column
FROM test_table
WHERE array_column && ARRAY['a', 'b'];

说明：

• 默认INTERSECT会对交集结果去重，如需保留重复元素可替换为INTERSECT ALL
• 若操作整数类型数组，且已安装intarray扩展，可直接使用&运算符快速求交集：array_column & ARRAY[1,2]，该运算符不支持文本等其他类型数组

执行上述SQL在提供的测试数据上，将返回预期结果：

id | array_column
---|-------------
1  | {'a'}
2  | {'b'}
5  | {'a','b'}

Python SQLAlchemy 对应实现 #

之前使用的是SQLAlchemy的ORM查询语法，可通过内置func构造对应数组交集逻辑，代码示例：

from sqlalchemy import func, cast, ARRAY, Unicode

# 定义目标匹配数组
target_array = cast(['a', 'b'], ARRAY(Unicode))

# 构造数组交集计算表达式
intersect_column = func.array(
    func.select(func.unnest(Table.array_column))
    .intersect(func.select(func.unnest(target_array)))
).label('array_column')

# 执行查询
query_result = query(Table.id, intersect_column).filter(
    Table.array_column.op('&&')(target_array)
).all()

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