python - SQLAlchemy:过滤存储在 JSONB 字段的嵌套列表中的值

Question

假设我有一个名为 Item 的模型，其中包含一个 JSONB 字段 data。其中一条记录存储了以下 JSON 对象:

{
    "name": "hello",
    "nested_object": {
        "nested_name": "nested"
    },
    "nested_list": [
        {
            "nested_key": "one"
        },
        {
            "nested_key": "two"
        }
    ]
}

我可以通过过滤 name 字段来找到这条记录:

Session().query(Item).filter(Item.data["name"] == "hello")

我可以像这样通过对嵌套对象进行过滤来找到这条记录:

Session().query(Item).filter(Item.data[("nested_object","nested_name")] == "hello")

但是，我正在努力寻找一种方法来通过过滤存储在嵌套列表中的项目的值来查找此记录。换句话说，如果用户提供了值“one”，我想找到上面的记录，并且我知道在 nested_list 中的键 nested_key 中查找它。

是否可以使用可用的 SQLAlchemy 过滤器来实现此目的？

Answer 1

SQLAlchemy 的 JSONB类型有 contains() Postgresql 中 @> 运算符的方法。 The @> operator用于检查左值是否在顶层包含正确的 JSON 路径/值条目。在你的情况下

data @> '{"nested_list": [{"nested_key": "one"}]}'::jsonb

或者在 python 中

the_value = 'one'

Session().query(Item).filter(Item.data.contains(
    {'nested_list': [{'nested_key': the_value}]}
))

该方法将您的 Python 结构转换为适合数据库的 JSON 字符串。

在 Postgresql 12 中，您可以使用 JSON path功能:

import json

Session().query(Item).\
    filter(func.jsonb_path_exists(
        Item.data,
        '$.nested_list[*].nested_key ? (@ == $val)',
        json.dumps({"val": the_value})))