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")
但是,我正在努力通过过滤存储在嵌套列表中的项目的值来寻找找到此记录的方法.换句话说,如果用户提供了值一",我想找到上面的记录,并且我知道要在 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
类型具有 contains()
方法用于 Postgresql 中的 @>
运算符.使用了@>
运算符检查左侧值是否包含顶层的右侧 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.
在 Postgresql 12 中,您可以使用 JSON 路径 功能:
In Postgresql 12 you can use the JSON path functions:
import json
Session().query(Item).
filter(func.jsonb_path_exists(
Item.data,
'$.nested_list[*].nested_key ? (@ == $val)',
json.dumps({"val": the_value})))
这篇关于SQLAlchemy:过滤存储在 JSONB 字段的嵌套列表中的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!