这是我的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" } ] }更多推荐
如何在MongoDB的每个组中分组和选择与max对应的文档?
发布评论