MongoDB find and return all with max value

Question

I'm trying to get a list of documents that have the max value. I can specify it if I know the number of documents with max value (like in the solution in this another stackoverflow solution 'mongodb how to get max value from collections'), but I don't know how to do it if I don't know what the number of documents is.

For example, using the following documents:

{name:"a", age:10}
{name:"b", age:11}
{name:"d", age:12}
{name:"c", age:12}

So I know that there are 2 documents with max age of 12. Therefore I can write the following query

db.collection.find().sort({age: -1).limit(2)

I use limit(2) because I know that there are 2 documents with a max value, but how can I automate that? Can I count the records with max value, store it in a variable and use it like limit(n)? Is there any other way to do it?

First find the max value using `db.collection.find({}, {"age": 1, _id: 0}).sort({age: -1).limit(1)`. Then filter documents based on the max value. If you define an index on `age`, the first query would be `Covering Index` query and very fast. — Ali Dehghani, Jun 02 '16 at 20:28
Possible duplicate of [mongodb how to get max value from collections](http://stackoverflow.com/questions/32076382/mongodb-how-to-get-max-value-from-collections) — Ali Dehghani, Jun 02 '16 at 20:40
@profesor79 Dup detection is a very tricky business i guess :) But imo, the first part of the query is more decisive and has been already asked. The second part seems very trivial. — Ali Dehghani, Jun 02 '16 at 20:45
@AliDehghani it's trivial if you know how :-), but SO is for those who want to know how! — profesor79, Jun 02 '16 at 20:48
Possible duplicate of [Find all documents that share max(value) found in aggregate step](http://stackoverflow.com/questions/36973145/find-all-documents-that-share-maxvalue-found-in-aggregate-step) — styvane, Jun 02 '16 at 23:04

profesor79 · Accepted Answer · 2016-06-02T20:52:34.923

you can use aggregation framework to get results

var group = {$group:{_id:"$age", names:{$push:"$name"}, count:{$sum:1}}}
var sort = {$sort:{"_id":-1}}
var limit= {$limit:1}
db.selrac.aggregate([group, sort, limit])

and output looks like this:

{
    "_id" : 12.0,
    "names" : [ 
        "d", 
        "c"
    ],
    "count" : 2.0
}

or if there is a need to have full document reference replace group by this:

var group = {$group:{_id:"$age", fullDocument:{$push:"$$ROOT"}, count:{$sum:1}}}

output:

{
    "_id" : 12.0,
    "fullDocument" : [ 
        {
            "_id" : ObjectId("57509a890d4ae20f6de06657"),
            "name" : "d",
            "age" : 12.0
        }, 
        {
            "_id" : ObjectId("57509a890d4ae20f6de06658"),
            "name" : "c",
            "age" : 12.0
        }
    ],
    "count" : 2.0
}

Any comments welcome!

MongoDB find and return all with max value

1 Answers1

Related