SQLAlchemy:过滤存储在JSONB字段的嵌套列表中的值 [英] SQLAlchemy: filtering on values stored in nested list of the JSONB field

问题描述

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

Lets say I have a model named Item, which contains a JSONB field data. One of the records has the following JSON object stored there:

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

我可以通过对name字段进行如下过滤来找到该记录:

I can find this record by filtering on the name field as such:

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

我同样可以通过过滤嵌套对象来找到该记录:

I can find this record by filtering on the nested object likewise as such:

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

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

However I am struggling to find a way to find this record by filtering on the value of the item stored within the nested list. In other words I want to find the record above if the user has provided value "one", and I know to look for it in the key nested_key within the nested_list.

是否可以使用可用的SQLAlchemy过滤器来实现这一目标?

Is it possible to achieve this with SQLAlchemy filters available?

推荐答案

SQLAlchemy的 JSONB 类型具有 @>运算符用于检查左侧值在顶层包含正确的JSON路径/值条目.就您而言

SQLAlchemy's JSONB type has the contains() method for the @> operator in Postgresql. The @> operator is used to check if the left value contains the right JSON path/value entries at the top level. In your case

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字符串.

The method converts your python structure to suitable JSON string for the database.

这篇关于SQLAlchemy:过滤存储在JSONB字段的嵌套列表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！