summerNight summerNight - 3 months ago 7
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

animals
:

{ "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
$max
operator in mongoDB but not sure about using that for different kinds of animals and filtering their age.

Answer

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:

db.animals.aggregate(
   [
     {
       $group:
         {
           _id: "$animal",
           maxAge: { $max: "$age" }
         }
     }
   ]
)

which should give you the result:

    [
       { "_id": "dog", "maxAge": 3},  
       { "_id": "cat", "maxAge": 4},    
       { "_id": "rabbit", "maxAge": 9} 
    ]