List collectiojs having max value in field

Working with Data
1

HI Friends. I need so simple mongodb query. I would be happy if you help::fire:

I have collection:

name, salary
john 2000
jack 1900
phill 2000
lamar 1500

I need that result :

name, salary
john 2000
phill 2000

Simply list collections where salary is equal max.

2

I am not sure this is the best approach but I would start experimenting with

  1. a $group stage with _id:null using the $max accumulator of salarary
  2. a $lookup stage to the starting collection with the result of 1. as the localField and salary as the foreignField
  3. an optional $unwind on the lookup results to have a list of document rather than 1 document with an array
3

Something like the following would work, but probably not the most efficient way to do things:

db.employees.aggregate(
    [{
            $facet: {
                salary: [{
                    $group: {
                        _id: null,
                        max: {
                            $max: "$salary"
                        }
                    }
                }],
                employees: [{
                    $match: {}
                }]
            }
        },
        {
            $project: {
                match: {
                    $filter: {
                        input: "$employees",
                        as: "employees",
                        cond: {
                            $eq: ["$$employees.salary", {
                                $first: "$salary.max"
                            }]
                        }
                    }
                }
            }
        }
    ]
)

You will want to adjust the employees facet to filter out any records you don’t want to work with so as to not push a lot of unnecessary data through the pipeline.

4

Welcome to the MongoDB Community @Bago_Bago !

The maximum salary value can be efficiently found by sorting in descending order using an index, which would be a better approach than iterating the whole collection to find a max value via aggregation accumulators like $group or $max.

You can explain your queries to see how they are processed including the ratio of index keys and documents examined compared to results returned.

Following is an example in the MongoDB shell (mongosh):

// Set up test data
db.salaries.insert([
	{name:'john',  salary: 2000},
	{name:'jack',  salary: 1900},
	{name:'phill', salary: 2000},
	{name:'lamar', salary: 1500}
])

// Add index
db.salaries.createIndex({salary:1})

// Find the max salary value
var maxSalary = db.salaries.find(
    {},                 // query matches all documents
    {_id:0, salary:1}   // projection limited to the indexed field to make this a covered query
).sort({salary:-1}).limit(1).next()

Excerpt from .explain("executionStats") output for this query:

nReturned: 1,
executionTimeMillis: 0,
totalKeysExamined: 1,
totalDocsExamined: 0,

This query only had to check a single index key (the max value based on the index order) and returned the matching value from the index without fetching any documents (which is called a covered query).

A quick check to confirm the value returned is suitable to be passed as the query parameter for find()

> maxSalary
{ salary: 2000 }

// Find all documents with max salary value
> db.salaries.find(maxSalary)
[
  {
    _id: ObjectId("631962b06fc808d36caace54"),
    name: 'john',
    salary: 2000
  },
  {
    _id: ObjectId("631962b06fc808d36caace56"),
    name: 'phill',
    salary: 2000
  }
]

Excerpt from .explain("executionStats") output for this query:

    nReturned: 2,
    executionTimeMillis: 0,
    totalKeysExamined: 2,
    totalDocsExamined: 2,

The ratio of index keys and documents examined is 1:1 with the number of result documents returned.

You could implement the same pattern in an aggregation query with $sort followed by $limit to find the maximum value and then a $lookup to fetch the matching documents, but will likely have to add some additional aggregation stages to get the desired result document format.

Regards,
Stennie

2 Likes