摘要：本文档介绍了如何使用 SQLAlchemy 和 PostgreSQL 过滤 JSON 类型字段中的数据。我们将探讨如何使用 cast() 函数将 JSON 类型转换为 JSONB 类型，并利用 has_any() 方法来高效地筛选出包含特定数组元素的记录。此外，还讨论了 JSONPath 的使用限制以及 SQLAlchemy 中 JSON 操作符的转换规则。
在使用 PostgreSQL 数据库时，经常会遇到存储 JSON 格式数据的情况。当需要根据 JSON 字段中的特定属性进行过滤时，可以使用 SQLAlchemy 来构建相应的查询语句。本文将详细介绍如何使用 SQLAlchemy 1.4 版本以及 PostgreSQL 9.6 或更高版本来过滤 JSON 类型字段。
JSON 数据准备
假设我们有一个名为 test_table 的表，其中包含一个 attributes 列，该列存储 JSON 数据。表结构如下：
Column | Type | ------------+------------------------+ id | integer | attributes | json |
表中的数据示例如下：
id | attributes
----+----------------------------
1 | {"a": 1, "b": ["b1","b2"]}
2 | {"a": 2, "b": ["b3"]}
3 | {"a": 3}现在，我们需要筛选出 attributes 字段中 b 属性包含 "b1" 或 "b3" 的记录。
使用 cast() 和 has_any() 进行过滤
在 PostgreSQL 中，可以使用 ?| 操作符来检查 JSON 数组是否包含指定数组中的任何元素。在 SQLAlchemy 中，?| 操作符对应于 has_any() 方法。要使用 has_any() 方法，首先需要将 JSON 类型转换为 JSONB 类型，因为 has_any() 方法是 JSONB 类型的比较器方法。
使用tbot机器ID身份文件配合tsh CLI，通过Teleport访问控制SSH登录托管主机或执行远程命令。
以下是使用 SQLAlchemy 实现上述过滤的示例代码：
from sqlalchemy import create_engine, Column, Integer, String, JSON, cast, select
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.dialects.postgresql import JSONB
# 定义模型
Base = declarative_base()
class TestTable(Base):
__tablename__ = 'test_table'
id = Column(Integer, primary_key=True)
attributes = Column(JSON)
def __repr__(self):
return f"<TestTable(id={self.id}, attributes={self.attributes})>"
# 创建数据库连接
engine = create_engine('postgresql://user:password@host:port/database')
Base.metadata.create_all(engine)
# 创建会话
Session = sessionmaker(bind=engine)
session = Session()
# 准备过滤条件
arr = ["b1", "b3"]
# 构建查询语句
stmt = select(TestTable).where(cast(TestTable.attributes["b"], JSONB).has_any(arr))
# 执行查询
results = session.execute(stmt).scalars().all()
# 打印结果
for result in results:
print(result)
# 关闭会话
session.close()在上面的代码中，我们首先使用 cast() 函数将 attributes["b"] 的类型转换为 JSONB。然后，我们使用 has_any() 方法来检查转换后的 JSONB 数组是否包含 arr 列表中的任何元素。
注意事项
- 确保 SQLAlchemy 版本为 1.4 或更高版本，PostgreSQL 版本为 9.6 或更高版本。
- has_any() 方法只能用于 JSONB 类型，因此需要使用 cast() 函数进行类型转换。
- 如果需要使用更灵活的 JSON 查询方式，可以考虑使用 JSONPath。但是，JSONPath 需要 PostgreSQL 12.0 或更高版本。
SQLAlchemy JSON 操作符转换规则
SQLAlchemy 将 PostgreSQL 的 JSON 操作符转换为相应的 Python 方法。以下是一些常见的转换规则：
| PostgreSQL 操作符 | SQLAlchemy 方法 |
|---|---|
| -> | __getitem__() |
| ->> | __getitem__() |
| ? | has_key() |
| ?| | has_any() |
| ?& | has_all() |
| @> | contains() |
| <@ | contained_by() |
更多详细的转换规则可以参考 SQLAlchemy 官方文档或相关 issue。
总结
本文介绍了如何使用 SQLAlchemy 和 PostgreSQL 过滤 JSON 类型字段中的数据。通过使用 cast() 函数将 JSON 类型转换为 JSONB 类型，并利用 has_any() 方法，可以高效地筛选出包含特定数组元素的记录。同时，我们也讨论了 JSONPath 的使用限制以及 SQLAlchemy 中 JSON 操作符的转换规则。希望本文能够帮助你更好地使用 SQLAlchemy 和 PostgreSQL 处理 JSON 数据。
