summerNight summerNight - 1 year ago 71
Scala Question

MongoDB: Best way to find different documents with their respective maximum values

I have the following documents in my DB in the collection


{ "animal": "dog", "age": 1}
{ "animal": "dog", "age": 2}
{ "animal": "dog", "age": 3}

{ "animal": "cat", "age": 1}
{ "animal": "cat", "age": 4}

{ "animal": "rabbit", "age": 9}

How do I return a result so that I get both dogs, cats and rabbits with the max age. Example:

{ "animal": "dog", "age": 3}, # oldest dog in DB
{ "animal": "cat", "age": 4}, # oldest cat in DB
{ "animal": "rabbit", "age": 9} # oldest rabbit in DB


I am trying to use the
operator in mongoDB but not sure about using that for different kinds of animals and filtering their age.

Answer Source

MongoDB's documentation on aggregation using the $max and $group operators should help you here. Basically, the $group operator will allow you to group the documents by the animal key, and then $max will allow you to query for the max age within each group.

Here's the query:

           _id: "$animal",
           maxAge: { $max: "$age" }

which should give you the result:

       { "_id": "dog", "maxAge": 3},  
       { "_id": "cat", "maxAge": 4},    
       { "_id": "rabbit", "maxAge": 9} 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download