如何用SQLAlchemy实现PostgreSQL JSONB数组的ANY匹配查询？

问题分析 #

你需要查询ListingDatabaseDocument表中doc->'features'数组同时包含"ELEVATOR"和"GARAGE"的行，原SQL语句是可行的，但你的SQLAlchemy代码存在两个核心问题：

• 错误地将字符串列表['ELEVATOR','GARAGE']转换为ARRAY(JSONB)类型，这会生成由单个JSONB字符串组成的数组，而非包含目标数组的JSONB值。
• any_的使用逻辑不符合原SQL的预期，原SQL中ANY作用于包含单个JSONB数组的数组，你的代码逻辑偏离了这个结构。

正确实现方案 #

方案1：直接匹配目标JSONB数组（推荐）

这是最简洁高效的写法，直接利用PostgreSQL JSONB的@>操作符检查左数组是否包含右数组的所有元素，完全匹配你的需求：

from sqlalchemy.dialects.postgresql import JSONB

return session.query(ListingDatabaseDocument).filter(
    ListingDatabaseDocument.doc['features'].op('@>')(JSONB(['ELEVATOR', 'GARAGE']))
).all()

或者使用func.jsonb_build_array构造目标数组，效果一致：

from sqlalchemy import func

return session.query(ListingDatabaseDocument).filter(
    ListingDatabaseDocument.doc['features'].op('@>')(func.jsonb_build_array('ELEVATOR', 'GARAGE'))
).all()

这两种写法都会生成等价于以下的SQL：

SELECT * FROM listing WHERE doc -> 'features' @> '["ELEVATOR","GARAGE"]'::jsonb

方案2：贴近原SQL的写法

如果你希望严格对齐原SQL中ANY(ARRAY[...]::jsonb[])的逻辑，可以按以下方式构造查询：

from sqlalchemy.dialects.postgresql import JSONB, ARRAY
from sqlalchemy import any_

# 构造包含目标JSONB数组的数组
target_jsonb_array = JSONB(['ELEVATOR', 'GARAGE'])
array_of_jsonb = ARRAY(JSONB).literal([target_jsonb_array])

return session.query(ListingDatabaseDocument).filter(
    ListingDatabaseDocument.doc['features'].op('@>')(any_(array_of_jsonb))
).all()

这段代码会生成与你原SQL完全一致的查询语句。

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