英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论