CODEWITHSUNDEEP
mongodbmongodb-query
Venkatesan Muniappan
Venkatesan Muniappan

Reputation: 447

MongoDB: Get all documents from collection filtered by max value of some field

I would like to know the best way to filter Mongo Collection by max value of some field. If I were to do the same thing in SQL I would apply the below query.

select * from tableName where column1 in (select max(column1) from tableName)

Given the below the sample input and output student. Here input dataset is filtered with the max value of StudentAge.

Input

_id StudentName StudentAge
1   John         15
2   David        16
3   Miller       15
4   Mike         16
5   Graham       16

Output

_id StudentName StudentAge
2   David        16
4   Mike         16
5   Graham       16

I tried looking up various questions on SO but I couldn't get across anything that matches my question. I am using Mongo Compass to query the Mongo collection.

Upvotes: 0

Views: 721

Answers (1)

hhharsha36
hhharsha36

Reputation: 3349

There is not as straight forward to do this in MongoDB although there are workarounds.

Method - 1:

One approach to implementing this is by using the MongoDB Aggregation query:

db.<Collection-Name>.aggregate([
    {
        "$group": {
            "_id": "$<Key-Name>",
            "documents": {"$push": "$$ROOT"}
        }
    },
    {
        "$sort": {
            "_id": -1
        }
    },
    {
        "$limit": 1
    },
    {
        "$unwind": "$documents"
    },
    {
        "$replaceRoot": { newRoot: "$documents" }
    },
])

Modify the <Collection-Name> and <Key-Name> with appropriate values.

This method is not recommended especially if the MongoDB collection consists of huge number of records in it.

Method - 2:

The second method is using the traditional MongoDB find method (although it involves two find commands.

var recordWithMaxValue = db.<Collection-Name>.find({}, {"<Key-Name>": 1}).sort({"<Key-Name>": -1}).limit(1);  // Init Cursor
db.<Collection-Name>.find({"<Key-Name>": recordWithMaxValue.next()["<Key-Name>"]});  // Get records with Max. value

This method is more recommended compared to the first one.

Please point out if there are any better solutions

Upvotes: 1

Related Questions