CODEWITHSUNDEEP
mongodbaggregation-framework
Anurag Sharma
Anurag Sharma

Reputation: 5039

Get all keys of a document having max keyvalue and grouped on another field

I have my documents like this in mongo 

{ "MappedTitle" : ObjectId("59e44696a4585255569f19d8"), "Title" : "Accounting Clerk", "MedianSalary" : 31200, "TimeToFill" : "Short" }
{ "MappedTitle" : ObjectId("59e44698a4585255569f19db"), "Title" : "Accounting Clerk", "MedianSalary" : 35360, "TimeToFill" : "Short" }
{ "MappedTitle" : ObjectId("59e44694a4585255569f19d7"), "Title" : "Controller (general)", "MedianSalary" : 63001, "TimeToFill" : "Medium" }
{ "MappedTitle" : ObjectId("59e44697a4585255569f19d9"), "Title" : "Controller (general)", "MedianSalary" : 75000, "TimeToFill" : "Medium" }
{ "MappedTitle" : ObjectId("59e44697a4585255569f19da"), "Title" : "Controller (general)", "MedianSalary" : 65000, "TimeToFill" : "Medium" }
{ "MappedTitle" : ObjectId("59e44694a4585255569f19d7"), "Title" : "Financial Reporting Manager", "MedianSalary" : 63001, "TimeToFill" : "Medium" }
{ "MappedTitle" : ObjectId("59e44697a4585255569f19d9"), "Title" : "Financial Reporting Manager", "MedianSalary" : 75000, "TimeToFill" : "Medium" }
{ "MappedTitle" : ObjectId("59e44697a4585255569f19da"), "Title" : "Financial Reporting Manager", "MedianSalary" : 65000, "TimeToFill" : "Medium" }
{ "MappedTitle" : ObjectId("59e446a3a4585255569f19e2"), "Title" : "Healthcare Administrator", "MedianSalary" : 58132, "TimeToFill" : "Medium" }
{ "MappedTitle" : ObjectId("59e446a5a4585255569f19e3"), "Title" : "Healthcare Administrator", "MedianSalary" : 38000, "TimeToFill" : "Short" }

I want to fetch the document with max MedianSalary grouped on Title field. I come up with this query - 

# titles is a list of titles for which I want to get the documents

db.MyCollection.aggregate({$match: {Title: {$in: titles}}}, {$group: {_id: "$Title", "maxValue": {$max: "$MedianSalary"}}}).pretty()
{ "_id" : "Financial Reporting Manager", "maxValue" : 75000 }
{ "_id" : "Controller (general)", "maxValue" : 75000 }
{ "_id" : "Healthcare Administrator", "maxValue" : 58132 }
{ "_id" : "Accounting Clerk", "maxValue" : 35360 }

Its giving the correct result but I want other fields -TimeToFill and MappedTitle of the matched document as well

Thanks in advance.

Upvotes: 0

Views: 29

Answers (1)

oneturkmen
oneturkmen

Reputation: 1330

Try the following snippet:

db.MyCollection.aggregate(
   {
      $match: {Title: {$in: titles}}
   }, 
   { 
      $group: {
         _id: "$Title", 
         "maxValue": {$max: "$MedianSalary"},
         "TimeToFill": {$first: "$TimeToFill"},
         "MappedTitle": {$first: "$MappedTitle"}
      }
   }).pretty()

You can use $first in order to get the rest of omitted fields while using $group. Let me know if this helps.

Upvotes: 1

Related Questions