我想获取特定字段中具有最高价值的“所有文档”,而不是按另一个字段分组。
考虑以下数据:
_id:1, country:india, quantity:12, name:xyz
_id:2, country:USA, quantity:5, name:abc
_id:3, country:USA, quantity:6, name:xyz
_id:4, country:india, quantity:8, name:def
_id:5, country:USA, quantity:10, name:jkl
_id:6, country:india, quantity:12, name:jkl
答案应该是
country:india max-quantity:12
name xyz
name jkl
country:USA max-quantity:10
name jkl
我已经尝试了几个查询,但是我只能获得没有名称的最大值,或者我可以分组,但它会显示所有值。
db.coll.aggregate([{
$group:{
_id:"$country",
"maxQuantity":{$max:"$quantity"}
}
}])
例如,上面将给出每个国家/地区的最大数量,但如何与其他字段组合以显示所有最大数量的单据。
If you want to keep document information, then you basically need to $push
it into an array. But of course, then having your $max
values, you need to filter the contents of the array for just the elements that match:
db.coll.aggregate([
{ "$group":{
"_id": "$country",
"maxQuantity": { "$max": "$quantity" },
"docs": { "$push": {
"_id": "$_id",
"name": "$name",
"quantity": "$quantity"
}}
}},
{ "$project": {
"maxQuantity": 1,
"docs": {
"$setDifference": [
{ "$map": {
"input": "$docs",
"as": "doc",
"in": {
"$cond": [
{ "$eq": [ "$maxQuantity", "$$doc.quantity" ] },
"$$doc",
false
]
}
}},
[false]
]
}
}}
])
So you store everything in an array and then test each array member to see if it's value matches the one that was recorded as the maximum, discarding any that do not.
I'd keep the _id
values in the array documents since that is what makes them "unique" and won't be adversely affected by $setDifference
when filtering out values. But of course if "name" is always unique then it won't be required.
You can also just return whatever fields you want from $map
, but I'm just returning the whole document for example.
请记住,这样做的局限性是不超过16MB的BSON大小限制,因此对于小数据样本也可以,但是任何产生潜在大列表(因为您无法预先过滤数组内容)的方法都可以使用分别查询以查找“ max”值,另一个查询以获取匹配的文档。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句