如何用纯SQLAlchemy实现PostgreSQL JSON字段的?|操作过滤

场景说明 #

我有一张test_table表，结构如下：

Column     |          Type          |
------------+------------------------+
 id         | integer                |
 attributes | json                   |

表中数据：

id |    attributes
----+----------------------------
  1 | {"a": 1, "b": ["b1","b2"]}
  2 | {"a": 2, "b": ["b3"]}
  3 | {"a": 3}

需要根据attributes字段中的b属性过滤数据，目前已通过LIKE方式实现，但希望等价于PostgreSQL原生的?|操作符查询：

SELECT * FROM test_table 
WHERE attributes -> 'b' ?| array['b1', 'b3'];

解决方案 #

可以用纯SQLAlchemy实现，以下是具体写法：

方法：使用op()构造操作符 #

直接对JSON字段的取值调用op()方法指定?|操作符，同时传入数组参数：

from sqlalchemy import select, cast
from sqlalchemy.dialects.postgresql import ARRAY, VARCHAR
# 替换为你的表模型导入路径
from your_module import test_table

target_values = ["b1", "b3"]
stmt = select(test_table).where(
    test_table.c.attributes["b"].op("?|")(cast(target_values, ARRAY(VARCHAR)))
)

效果验证 #

执行上述代码后，生成的SQL与目标原生查询完全一致，返回结果如下：

id |    attributes
----+----------------------------
  1 | {"a": 1, "b": ["b1","b2"]}
  2 | {"a": 2, "b": ["b3"]}

原理说明：SQLAlchemy会将test_table.c.attributes["b"]解析为attributes -> 'b'，配合op("?|")和类型转换后的数组参数，完美匹配PostgreSQL的?|操作符逻辑，实现JSON数组的包含匹配。

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