使用 mongodb 中的聚合获取所有具有最大值的文档 [英] get all the documents having max value using aggregation in mongodb

问题描述

I want to fetch "all the documents" having highest value for specific field and than group by another field.

Consider below data:

_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

Answer should be

country:india max-quantity:12
name xyz
name jkl 

country:USA max-quantity:10
name jkl

I have tried several queries, but I can get only the max value without the name or i can go group by but it shows all the values.

db.coll.aggregate([{
    $group:{
        _id:"$country",
        "maxQuantity":{$max:"$quantity"}
    }
}])

for example above will give max quantity on every country but how to combine with other field such that it shows all the documents of 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.

Keep in mind that this has the limitation of not exceeding the BSON size limit of 16MB, so is okay for small data samples, but anything producing a potentially large list ( since you cannot pre-filter array content ) would be better of processed with a separate query to find the "max" values, and another to fetch the matching documents.

这篇关于使用 mongodb 中的聚合获取所有具有最大值的文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！