Geek Answers Handbook

How to group and select the document corresponding to max in each group in MongoDB?

Here is my collection of mongo collections:

{"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"}

I would like to group the hash and return the document with the largest "num_sold". Since the conclusion I would like to see:

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

I know the basic aggregate operator, and here is how I could group and get the maximum num_sold, but I need the whole document that matches the maximum, not just the value.

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

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

+2
source share
2 answers

You can use $redact to accomplish this. This avoids the use of $sort , and then runs $group or $unwind again.

  • $group on _id and get maximum max_num_sold for each group, copy all documents in the group using $push .
  • $redact in subtopies per group, storing only those with a maximum max_num_sold in their num_sold

code example:

 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"]}}, ])

test data:

 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"} ])

test result:

 { "_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" } ] }
+2
source

It seems that grouping in mongodb does not distort the order and something like this is possible:

mongodb, how to aggregate with a group and sort correctly .

In particular, for the above example, we can get the following:

 db.getCollection('sales').aggregate([ {$sort: {"num_sold":-1}}, {$group:{"_id": "$hash", "max_num_sold" : {$first:"$num_sold"}, "title":{$first: "$title"}, "place":{$first:"$place"} }} ])

It displays here:

 { "result" : [ { "_id" : 17.0000000000000000, "max_num_sold" : 55.0000000000000000, "title" : "Baz", "place" : "JKN" }, { "_id" : 18.0000000000000000, "max_num_sold" : 55.0000000000000000, "title" : "Bar", "place" : "CDF" } ], "ok" : 1.0000000000000000 }
0
source

More articles:


All Articles