使用 PostgreSQL 和 SQLAlchemy 查询嵌套 JSONB 字段-Python教程-PHP中文网

使用 PostgreSQL 和 SQLAlchemy 查询嵌套 JSONB 字段

心靈之曲

发布： 2025-09-29 16:45:01

原创

618人浏览过

使用 postgresql 和 sqlalchemy 查询嵌套 jsonb 字段

本文档详细介绍了如何使用 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 中的键需要用双引号括起来。
未找到结果

Alkaid.art
专门为Phtoshop打造的AIGC绘画插件

153

查看详情

即使语法正确，如果 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 数据。

以上就是使用 PostgreSQL 和 SQLAlchemy 查询嵌套 JSONB 字段的详细内容，更多请关注php中文网其它相关文章！