本文档详细介绍了如何使用 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_notionWHERE 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, Stringfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.dialects.postgresql import JSONB, JSONPATHfrom sqlalchemy import textBase = 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 数据。
以上就是使用 PostgreSQL 和 SQLAlchemy 查询嵌套 JSONB 字段的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/1374470.html
微信扫一扫 
支付宝扫一扫 
