Erimus Koo Erimus Koo - 12 days ago 5
Python Question

mongodb how to get max value of each "group with the same key"

I have a collection:

{'name':'ada','updateTime':'2016-11-25'}
{'name':'bob','updateTime':'2016-11-25'}
{'name':'ada','updateTime':'2016-11-20'}
{'name':'bob','updateTime':'2016-11-20'}
{'name':'ada','updateTime':'2016-11-15'}
{'name':'bob','updateTime':'2016-11-15'}
...


if I want the result is, the max value of 'updateTime' of the same 'name':

{'name':'ada','updateTime':'2016-11-25'}
{'name':'bob','updateTime':'2016-11-25'}
...


or finaly get a python dict:

{'ada':'2016-11-25','bob':'2016-11-25',...}


How to do it most effective?

I do it now in python is:

for name in db.collection.distinct('name'):
result[name]=db.collection.find({'name':name}).sort('updateTime',-1)[0]


is it do 'find' too many times?

Answer

you can do this in a single query with aggregation :

db.collection.aggregate([
   {
      $sort:{
         updateTime:-1
      }
   },
   {
      $group:{
         "_id":"$name",
         updateTime:{
            $first:"$updateTime"
         }
      }
   }
])

this will return

{ "_id" : "bob", "updateTime" : "2016-11-25" }
{ "_id" : "ada", "updateTime" : "2016-11-25" }