英文:
MongoDB match document's array element based on condition defined in the element's value
问题
我在我的数据库中有一个 MongoDB 文档(v6.0.3
):
{
"user": "123",
"alerts": [
{
"alertDirection": "$gte",
"alertValue": 9999,
"location": "home",
"available": false,
"position": 28
},
{
"alertDirection": "$lte",
"alertValue": 50,
"location": "home",
"available": true,
"position": 14
},
{
"alertDirection": "$gte",
"alertValue": 100,
"location": "home",
"available": true,
"position": 71
},
{
"alertDirection": "$gte",
"alertValue": 100,
"location": "out",
"available": true,
"position": 43
}
]
}
我根据一些条件匹配需要的数组元素:
{
"$match": {
"alerts": {
"$elemMatch": {
"$and": [
{ "location": "home" },
{ "available": true }
]
}
}
}
}
是否可能扩展我的 $match
以仅获取匹配 alertValue
基于 alertDirection
值和我拥有的变量的文档?
例如,我有一个值为 10
的变量 myValue
。我想仅匹配数组中 myValue
大于或小于 alertValue
的元素,这取决于 alertDirection
。在我的示例中,唯一的匹配将是以下元素(因为 10
是 小于或等于
50
):
{
"alertDirection": "$lte",
"alertValue": 50,
"location": "home",
"available": true,
"position": 14
}
或者如果 myValue
是 100
,则只有以下元素将被匹配(因为 100
是 大于或等于
100
):
{
"alertDirection": "$gte",
"alertValue": 100,
"location": "home",
"available": true,
"position": 71
}
英文:
I have a MongoDB (v6.0.3
) document in my database:
{
"user": "123",
"alerts": [
{
"alertDirection": "$gte",
"alertValue": 9999,
"location": "home",
"available": false,
"position": 28
},
{
"alertDirection": "$lte",
"alertValue": 50,
"location": "home",
"available": true,
"position": 14
},
{
"alertDirection": "$gte",
"alertValue": 100,
"location": "home",
"available": true,
"position": 71
},
{
"alertDirection": "$gte",
"alertValue": 100,
"location": "out",
"available": true,
"position": 43
}
]
}
And I'm matching array elements I need based on few conditions:
{
$match: {
"alerts": {
$elemMatch: {
$and: [
{ "location": "home" },
{ "available": true }
]
}
}
}
}
Is it possible to expand my $match
to fetch only documents with alerts
elements matching alertValue
based on alertDirection
value and a variable I have?
For example, I have a variable myValue
with value 10
. I'd like to match only elements in array where myValue
is either higher or lower than alertValue
, depending on alertDirection
. In my example, the only match would be the following element (because 10
is lower or equal
than 50
):
{
"alertDirection": "$lte",
"alertValue": 50,
"location": "home",
"available": true,
"position": 14
}
Or if myValue
would be 100
, only the following element would be matched (because 100
is greater or equal
than 100
):
{
"alertDirection": "$gte",
"alertValue": 100,
"location": "home",
"available": true,
"position": 71
}
答案1
得分: 1
以下是翻译好的部分:
你可以尝试这个查询:
关键点在于使用 $cond
,如果 "direction" 是 $gte
,那么就与 $gte
进行比较,否则与 $lte
进行比较(但要小心使用 $literal
获取字符串文字。
最后一步是分组,以相同的方式将结果放入数组中,就像在数据库中一样,但这是可选的。
db.collection.aggregate([
{
"$unwind": "$alerts"
},
{
"$match": {
"alerts.location": "home",
"alerts.available": true,
"$expr": {
"$cond": {
"if": {
"$eq": ["$alerts.alertDirection", {"$literal": "$gte"}]
},
"then": {"$gte": [yourValue, "$alerts.alertValue"]},
"else": {"$lte": [yourValue, "$alerts.alertValue"]}
}
}
}
},
{
"$group": {
"_id": "$_id",
"user": {
"$first": "$user"
},
"alerts": {
"$push": "$alerts"
}
}
}
])
示例在这里。
英文:
You can try this query:
The trick here is to use $cond
and if the "direction" is $gte
then compare with $gte
otherwise $lte
(but be careful to use $literal
to get the string literal.
And the last step is a group to get result into an array in the same way that are into DB but is optional.
db.collection.aggregate([
{
"$unwind": "$alerts"
},
{
"$match": {
"alerts.location": "home",
"alerts.available": true,
"$expr": {
"$cond": {
"if": {
"$eq": ["$alerts.alertDirection",{"$literal": "$gte"}]
},
"then": {"$gte": [yourValue,"$alerts.alertValue"]},
"else": {"$lte": [yourValue,"$alerts.alertValue"]}
}
}
}
},
{
"$group": {
"_id": "$_id",
"user": {
"$first": "$user"
},
"alerts": {
"$push": "$alerts"
}
}
}
])
Example here
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论