本文档详细介绍了如何使用 PostgreSQL、SQLAlchemy 和 Python 查询包含深度嵌套对象的 JSONB 列。我们将探讨如何构建正确的 JSONPath 查询，以递归搜索 JSONB 对象，并提取具有特定键的对象。本文档提供了一个实用的解决方案，避免了常见的语法错误，并展示了如何有效地利用 PostgreSQL 的 JSONB 功能。
理解 JSONB 和 JSONPath
PostgreSQL 的 JSONB 数据类型允许您以二进制格式存储 JSON 数据，从而提高查询效率。JSONPath 是一种查询 JSON 文档的语言，类似于 XPath 用于 XML 文档。结合使用 JSONB 和 JSONPath，您可以执行复杂的查询，以提取嵌套在 JSON 文档中的特定数据。
常见错误和解决方法
在使用 jsonb_path_query 函数时，您可能会遇到一些常见的语法错误。以下是这些错误以及如何解决它们：
-
语法错误：syntax error at or near "$"
此错误通常是由于 JSONPath 表达式未正确引用造成的。JSONPath 表达式必须用单引号括起来。
-
语法错误：trailing junk after numeric literal
当 JSONPath 包含 UUID 等非数字字符串作为键时，可能会发生此错误。JSONPath 中的键需要用双引号括起来。
-
未找到结果
即使语法正确，如果 JSONPath 表达式没有使用 strict 模式，也可能无法找到预期的结果。建议在使用 .** 访问器时，始终使用 strict 模式。
正确的 JSONPath 查询示例
以下示例展示了如何使用 jsonb_path_query 函数和 strict 模式来查询嵌套的 JSONB 对象。
SELECT jsonb_path_query(record_map,
'strict $.**?(@.keyvalue().key==$target_id)',
jsonb_build_object('target_id',
'7a9abf0d-a066-4466-a565-4e6d7a960a37'))
FROM private_notion
WHERE site_id = '45bf37be-ca0a-45eb-838b-015c7a89d47b';解释：
- record_map: 这是包含 JSONB 数据的列名。
- 'strict $.**?(@.keyvalue().key==$target_id)': 这是 JSONPath 表达式。
- strict: 强制执行严格模式，确保查询的准确性。
- $.**: 递归地搜索 JSON 文档的所有级别。
- ?(@.keyvalue().key==$target_id): 过滤条件，查找键与 $target_id 匹配的对象。
- jsonb_build_object('target_id', '7a9abf0d-a066-4466-a565-4e6d7a960a37'): 创建一个 JSON 对象，将目标 UUID 传递给 $target_id 变量。
- FROM private_notion: 指定要查询的表。
- WHERE site_id = '45bf37be-ca0a-45eb-838b-015c7a89d47b': 添加一个筛选条件，用于指定要查询的 site_id。
此查询将返回 record_map 中所有包含键为 '7a9abf0d-a066-4466-a565-4e6d7a960a37' 的对象，无论它们嵌套在 JSON 结构的哪个级别。
在 SQLAlchemy 中使用 JSONPath
SQLAlchemy 提供了 JSONPath 类型，可以用于在 Python 代码中构建和执行 JSONPath 查询。
from sqlalchemy import create_engine, Column, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.dialects.postgresql import JSONB, JSONPATH
from sqlalchemy import text
Base = declarative_base()
class PrivateNotion(Base):
__tablename__ = 'private_notion'
id = Column(String, primary_key=True)
site_id = Column(String)
record_map = Column(JSONB)
engine = create_engine('postgresql://user:password@host:port/database')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
target_id = '7a9abf0d-a066-4466-a565-4e6d7a960a37'
site_uuid = '45bf37be-ca0a-45eb-838b-015c7a89d47b'
# 使用 text 方法构建查询
stmt = text(
"""
SELECT jsonb_path_query(record_map,
'strict $.**?(@.keyvalue().key==$target_id)',
jsonb_build_object('target_id', :target_id))
FROM private_notion
WHERE site_id = :site_id
"""
).params(target_id=target_id, site_id=site_uuid)
results = session.execute(stmt).scalars().all()
for result in results:
print(result)
session.close()解释：
- 首先，我们定义了 PrivateNotion 模型，它映射到 private_notion 表。
- 然后，我们创建了一个 SQLAlchemy 引擎，并使用它来创建数据库表。
- 接下来，我们创建了一个 SQLAlchemy 会话。
- 使用 text 函数构建原始 SQL 查询，并使用参数 :target_id 和 :site_id 来防止 SQL 注入。
- 我们使用 session.execute(stmt).scalars().all() 执行查询并获取结果。
- 最后，我们遍历结果并打印它们。
注意事项
- 始终使用单引号括起 JSONPath 表达式。
- 在 JSONPath 表达式中，使用双引号括起键。
- 使用 strict 模式可以提高查询的准确性。
- 使用 SQLAlchemy 的 JSONPath 类型可以更方便地构建和执行 JSONPath 查询。
- 使用参数化查询防止 SQL 注入。
总结
通过理解 JSONB 数据类型和 JSONPath 查询语言，您可以有效地从 PostgreSQL 数据库中的嵌套 JSON 文档中提取数据。本文档提供了一个实用的指南，帮助您避免常见的错误，并构建正确的 JSONPath 查询。 通过结合使用 PostgreSQL、SQLAlchemy 和 Python，您可以构建强大的应用程序，以处理和分析复杂的 JSON 数据。
