如何在SQLAlchemy中实现「字面量存在于数组列」的过滤？

没问题！SQLAlchemy完全支持这种「字面量存在于数组列」的反向过滤，不过得根据你用的数据库类型来选择对应的写法，我分两种常见场景给你讲：

1. PostgreSQL 数组列查询 #

PostgreSQL原生支持数组类型，SQLAlchemy对它的适配很完善，有两种常用写法：

写法一：使用contains()方法 #

这个方法对应PG的@>操作符，检查数组列是否包含指定的元素集合：

# 假设你的模型定义了数组列numbers = Column(ARRAY(Integer))
results = session.query(MyTable).filter(MyTable.numbers.contains([1])).all()

对应的SQL语句：

SELECT * FROM my_table WHERE numbers @> ARRAY[1]::integer[]

写法二：使用any()方法 #

这个方法对应PG的= ANY()语法，直接判断字面量是否存在于数组中：

results = session.query(MyTable).filter(1 == MyTable.numbers.any()).all()

对应的SQL语句：

SELECT * FROM my_table WHERE 1 = ANY(numbers)

2. BigQuery 重复字段查询 #

BigQuery的重复字段本质是数组，SQLAlchemy可以通过两种方式实现需求：

写法一：使用func.any()构造函数 #

直接调用BigQuery支持的ANY()函数，对应你想要的WHERE 1 IN UNNEST(numbers)效果：

from sqlalchemy import func

# 模型中定义重复字段：numbers = Column('numbers', 'ARRAY<INT64>')
results = session.query(MyTable).filter(1 == func.any(MyTable.numbers)).all()

对应的SQL语句：

SELECT * FROM my_table WHERE 1 = ANY(numbers)

写法二：显式构造UNNEST子查询 #

如果你想严格对应WHERE 1 IN UNNEST(numbers)的写法，可以用存在子查询实现：

subquery = session.query(func.unnest(MyTable.numbers).label('n')).filter(func.unnest(MyTable.numbers) == 1).exists()
results = session.query(MyTable).filter(subquery).all()

对应的SQL语句：

SELECT * FROM my_table WHERE EXISTS (
    SELECT 1 FROM UNNEST(numbers) AS n WHERE n = 1
)

通用提示 #

• 不同数据库的数组类型语法有差异，SQLAlchemy会根据你使用的dialect自动转换为对应数据库的原生SQL
• 如果你用的是其他支持数组的数据库（比如MySQL 8.0+的JSON数组），可以类似地使用func.json_contains()或者对应的数据库函数来实现

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