如何查询具有深度嵌套对象的JSONB列，这些对象在PythonFastAPI、SqlAlChemy和PostgreSQL中

在一个PostgreSQL表"PRIVATE_INNOTION"中，我有一个JSONB列"RECORD_MAP"，它可能包含也可能不包含嵌套对象，例如.

{
  "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-a565-4e6d7a960a39": {
              "name": "block5",
              "value": 5,
              "child": {
                "7a9abf0d-a066-4466-a565-4e6d7a960a40": {
                  "name": "block6",
                  "value": 6
                }
              }
            }
          }
        },
      }
    }
  }
}

要检索数据，我们不知道哪个块有我们想要的数据，我们只有密钥.让我们假设我们正在寻找这个键为"7a 9abf 0 d-a066 -4466-a565- 4 e6 d 7a 960 a40"的对象，但我们不知道它位于父块4和块5的子块6中.另一个请求可能会寻找父块4等等，我必须通过它的键找到块.

整个代码如下所示；

async def get_private_notion_page(
        site_uuid: str, page_id: str, db_session: AsyncSession
    ) -> PrivateNotionPage:
       
        page_id_path = f"{page_id}" # page_id looks like this 7a9abf0d-a066-4466-a565-4e6d7a960a37
        path = f"$.** ? (@.{page_id_path})"

        stmt = text(
            f"""
            SELECT jsonb_path_query(record_map, {path})
            FROM private_notion
            WHERE site_id = {site_uuid}
            """
        )

        result = await db_session.execute(stmt)
        result = result.scalars().first()

        if result:
            return result
        else:
            raise PrivateNotionSiteWasNotFound

因此，我想出了下面的查询语句，这些语句使用了SQL"Text"方法来接受原始的SQL查询，但是jsonb_path_query_array和jsonb_path_query抛出了类似的错误；syntax error at or near "$".

page_id_path = f"{page_id}"
path = f"$.** ? (@.{page_id_path})"
stmt = text(
            f"""
            SELECT jsonb_path_query(record_map, {path})
            FROM private_notion
            WHERE site_id = {site_uuid}
            """
        )

Error:

sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.PostgresSyntaxError'>: syntax error at or near "$"
[SQL: 
             SELECT jsonb_path_query(record_map, $.** ? (@.7a9abf0d-a066-4466-a565-4e6d7a960a37))
             FROM private_notion
             WHERE site_id = 26f52d8e-a380-46ab-9131-e6f7f62c528f
]

我后来了解到"$**运算符在SQL查询中无效.相反，您可以使用jsonb_路径_查询_数组函数递归地搜索所有级别的JSONB对象."

显然，在重构代码后，我也遇到了同样的错误.

page_id_path = f"{page_id}"
path = f"$[*] ? (@ like_regex {page_id_path})"
stmt = text(
            f"""
            SELECT jsonb_path_query_array(record_map -> 'block', {path})
            FROM private_notion
            WHERE site_id = {site_uuid}
            """
        )

Error:

sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.PostgresSyntaxError'>: syntax error at or near "$"
[SQL: 
     SELECT jsonb_path_query_array(record_map -> 'block', $[*] ? (@ like_regex 7a9abf0d-a066-4466-a565-4e6d7a960a37))
     FROM private_notion
     WHERE site_id = 26f52d8e-a380-46ab-9131-e6f7f62c528f
]

我的问题是双管齐下的，到底是什么错误？还有在JSONB列中通过键检索嵌套对象的更好方法吗？谢谢您抽时间见我.