使用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.

考虑以下数据:

_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

我已经尝试了几次查询，但是我只能获得没有名称的最大值，或者我可以分组，但它会显示所有值.

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.

推荐答案

如果要保留文档信息，则基本上需要 $push 放入数组.但是，当然，然后使用您的 $max 值，您只需要为匹配的元素过滤数组的内容:

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.

我将_id值保留在数组文档中，因为这是使它们唯一"的原因，并且不会受到

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.

您还可以从 $map 中返回所需的任何字段，但是我例如，我只是返回整个文档.

You can also just return whatever fields you want from $map, but I'm just returning the whole document for example.

请记住，这样做的局限性是不超过16MB的BSON大小限制，因此对于小数据样本也可以，但是任何产生潜在大列表(因为您无法预先过滤数组内容)的方法都更好.用单独的查询进行处理以找到最大"值，然后使用另一个查询来获取匹配的文档.

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屋！