SQLAlchemy：如何筛选JSONB数组字段中字典的指定值

我的Postgres表中有一个JSONB类型的data字段，存储格式为数组，示例数据如下：

entity.data = [{"a": 1, "b": "name"}, {"a": 2, "b": "name1"}]

我需要查找数组中存在b == "name1"的记录，但尝试以下筛选语句未生效（推测是因为该语句仅适用于字典类型的JSONB，而非数组）：

record = TableName.query.filter(TableName.data['b'].astext == 'name1').first()

针对JSONB数组的查询，可通过以下几种方式实现：

方法1：使用@>包含操作符 #

利用Postgres原生的@>操作符，检查JSONB数组是否包含指定对象：

record = TableName.query.filter(TableName.data.op('@>')('[{"b": "name1"}]')).first()

该操作符会判断data数组中是否存在至少一个符合{"b": "name1"}的元素。

方法2：使用jsonb_path_exists函数 #

通过JSON路径表达式精准匹配数组元素：

from sqlalchemy import func

record = TableName.query.filter(
    func.jsonb_path_exists(TableName.data, '$.b ? (@ == "name1")')
).first()

路径表达式$.b ? (@ == "name1")的作用是检查数组内是否存在元素的b属性等于name1。

方法3：展开数组后筛选 #

通过jsonb_array_elements将数组拆分为单行数据，再进行条件匹配：

from sqlalchemy import func, exists

subquery = exists().where(
    func.jsonb_array_elements(TableName.data).op('->>')('b') == 'name1'
)
record = TableName.query.filter(subquery).first()

这种方式会把数组的每个元素单独拆成行，再检查是否有行满足b字段等于name1的条件。

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