本文介绍了如何在 PostgreSQL 数据库中，使用 SQLAlchemy 和 Python 查询包含深度嵌套对象的 JSONB 列。我们将探讨如何使用 jsonb_path_query 函数以及 JSONPath 表达式来高效地检索所需数据，并解决常见的语法错误。通过本文，你将掌握一种更灵活、强大的 JSONB 数据查询方法。
理解 JSONB 和 JSONPath
PostgreSQL 的 JSONB 数据类型允许你存储 JSON（JavaScript Object Notation）数据，并对其进行高效的查询。JSONPath 是一种查询 JSON 数据的语言，类似于 XPath 用于 XML 数据。
在处理嵌套的 JSONB 对象时，直接访问深层嵌套的数据可能比较困难。这时，jsonb_path_query 函数结合 JSONPath 表达式就显得非常强大。
使用 jsonb_path_query 查询嵌套对象
假设我们有一个名为 private_notion 的表，其中包含一个名为 record_map 的 JSONB 列，该列存储了嵌套的 JSON 对象。我们的目标是根据特定的键（例如 UUID）在 record_map 中查找对象。
以下是一个示例 JSON 结构：
{ "blocks": { "7a9abf0d-a066-4466-a565-4e6d7a960a37": { "name": "block1", "value": 1, "child": { "7a9abf0d-a066-4466-a565-4e6d7a960a37": { "name": "block2", "value": 2, "child": { "7a9abf0d-a066-4466-a565-4e6d7a960a37": { "name": "block3", "value": 3 } } }, "7a9abf0d-a066-4466-a565-4e6d7a960a38": { "name": "block4", "value": 4, "child": { "7a9abf0d-a066-4466-4466-a565-4e6d7a960a39": { "name": "block5", "value": 5, "child": { "7a9abf0d-a066-4466-a565-4e6d7a960a40": { "name": "block6", "value": 6 } } } } } } } }}
要查找包含特定 UUID 的对象，可以使用以下 SQL 查询：
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';
这个查询使用了 jsonb_path_query 函数，并传入了以下参数：
record_map: 要查询的 JSONB 列。’strict $.**?(@.keyvalue().key==$target_id)’: JSONPath 表达式，用于递归搜索 JSON 对象，查找键等于 $target_id 的对象。strict 模式确保了表达式的严格匹配。jsonb_build_object(‘target_id’, ‘7a9abf0d-a066-4466-a565-4e6d7a960a37’): 创建一个 JSON 对象，将 target_id 设置为要查找的 UUID。
在 SQLAlchemy 中使用 jsonb_path_query
在 SQLAlchemy 中，可以使用 text 方法执行原始 SQL 查询。以下是一个示例：
from sqlalchemy import textfrom sqlalchemy.ext.asyncio import AsyncSessionasync def get_private_notion_page( site_uuid: str, page_id: str, db_session: AsyncSession) -> dict: """ Retrieves a nested object from a JSONB column by key using jsonb_path_query. """ query = text( """ SELECT jsonb_path_query(record_map, 'strict $.**?(@.keyvalue().key==$target_id)', jsonb_build_object('target_id', :page_id)) FROM private_notion WHERE site_id = :site_uuid """ ) result = await db_session.execute(query, {"page_id": page_id, "site_uuid": site_uuid}) result = result.scalars().first() return result
在这个例子中，我们使用了参数化查询，将 page_id 和 site_uuid 作为参数传递给查询，避免了 SQL 注入的风险。
常见错误和解决方法
在尝试使用 jsonb_path_query 时，可能会遇到一些常见的错误。以下是一些解决方法：
语法错误： 确保 JSONPath 表达式使用单引号括起来。UUID 格式错误： 确保 UUID 在 JSONPath 表达式中用双引号括起来。未启用 strict 模式： 建议在使用 .** 访问器时，始终启用 strict 模式，以避免意外的结果。
使用 SQLAlchemy JSONPath 类型
从 SQLAlchemy 2.0 开始，你可以使用 JSONPath 类型来更安全地传递 JSONPath 表达式。
from sqlalchemy.dialects.postgresql import JSONPathfrom sqlalchemy import column, table, selectprivate_notion_table = table( "private_notion", column("record_map"), column("site_id"),)def get_private_notion_page(site_uuid: str, page_id: str): """ Retrieves a nested object from a JSONB column by key using jsonb_path_query and SQLAlchemy JSONPath. """ target_id = "7a9abf0d-a066-4466-a565-4e6d7a960a37" jsonpath_expression = "strict $.**?(@.keyvalue().key==$target_id)" stmt = select( func.jsonb_path_query( private_notion_table.c.record_map, jsonpath_expression, func.jsonb_build_object("target_id", target_id), ) ).where(private_notion_table.c.site_id == site_uuid) # Execute the statement using your database session # result = await db_session.execute(stmt) # return result.scalars().first() return stmt # Returning the statement for demonstration
总结
通过本文，你学习了如何使用 PostgreSQL 的 jsonb_path_query 函数和 JSONPath 表达式，结合 SQLAlchemy，高效地查询嵌套的 JSONB 数据。 掌握这些技术，可以让你更灵活地处理 JSONB 数据，并构建更强大的应用程序。记住，正确地使用 JSONPath 表达式，并注意常见的错误，是成功查询 JSONB 数据的关键。
以上就是使用 PostgreSQL 和 SQLAlchemy 查询嵌套 JSONB 列的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/1374466.html
微信扫一扫 
支付宝扫一扫 
