如何在MongoDB的每个组中分组和选择与max对应的文档?

本文介绍了如何在MongoDB的每个组中分组和选择与max对应的文档?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这是我的mongo系列"sales":

Here is my mongo collection 'sales':

{"title":"Foo", "hash": 17, "num_sold": 49, "place": "ABC"} {"title":"Bar", "hash": 18, "num_sold": 55, "place": "CDF"} {"title":"Baz", "hash": 17, "num_sold": 55, "place": "JKN"} {"title":"Spam", "hash": 17, "num_sold": 20, "place": "ZSD"} {"title":"Eggs", "hash": 18, "num_sold": 20, "place": "ZDF"}

我想按哈希分组并返回"num_sold"最大的文档.因此，作为输出，我希望看到:

I would like to group by hash and return document with the greatest "num_sold". So as output I would like to see:

{"title":"Baz", "hash": 17, "num_sold": 55, "place": "JKN"} {"title":"Bar", "hash": 18, "num_sold": 55, "place": "CDF"}

我知道聚合运算符的基本知识，这是如何对num_sold进行分组和获得最大值，但是我需要整个文档对应于最大值，而不仅仅是值.

I know basic of aggregate operator and here is how I would group and get maximum of num_sold, but I need whole document corresponding to maximum, not just the value.

db.getCollection('sales').aggregate([ {$group: {_id: "$hash", max_sold : {$max: '$value'}}} ])

在SQL中，我可以使用join来完成，但是在mongo中.我还读到在mongo group和sort中不能很好地协同工作.

In SQL I would have done it with join, but in mongo. I also read that in mongo group and sort do not work well together.

推荐答案

您可以使用 $redact 阶段即可完成此操作.避免使用 $sort ，然后再次使用 $group 或 $unwind .

You can use the $redact stage to accomplish this. It avoids the usage of $sort and then again doing a $group or an $unwind.

• $group乘_id并获得每个组的最大max_num_sold，使用 $push 运算符.
将
• $redact分成每个组的子文档，仅将num_sold中最大max_num_sold的那些保留在其子文档中.

• $group by _id and get the maximum max_num_sold for each group, accumulate all the documents in the group using the $push operator.
• $redact into sub documents per group, keeping only those which have the maximum max_num_sold in their num_sold

示例代码:

db.getCollection('sales').aggregate([ {$group:{"_id":"$hash", "max_num_sold":{$max:"$num_sold"}, "records":{$push:"$$ROOT"}}}, {$redact:{$cond:[{$eq:[{$ifNull:["$num_sold","$$ROOT.max_num_sold"]}, "$$ROOT.max_num_sold"]}, "$$DESCEND","$$PRUNE"]}}, ])

测试数据:

db.getCollection('sales').insert([ {"title":"Foo","hash":17,"num_sold":49,"place":"ABC"}, {"title":"Bar","hash":18,"num_sold":55,"place":"CDF"}, {"title":"Baz","hash":17,"num_sold":55,"place":"JKN"}, {"title":"Spam","hash":17,"num_sold":20,"place":"ZSD"}, {"title":"Eggs","hash":18,"num_sold":20,"place":"ZDF"} ])

测试结果:

{ "_id" : 18, "max_num_sold" : 55, "records" : [ { "_id" : ObjectId("567874f2b506fc2193a22696"), "title" : "Bar", "hash" : 18, "num_sold" : 55, "place" : "CDF" } ] } { "_id" : 17, "max_num_sold" : 55, "records" : [ { "_id" : ObjectId("567874f2b506fc2193a22697"), "title" : "Baz", "hash" : 17, "num_sold" : 55, "place" : "JKN" } ] }