MongoDB Exercise - Find the restaurants with the highest total score

Write a MongoDB query to find the restaurants that achieved the highest total score.

Structure of 'restaurants' collection :

{
  "address": {
     "building": "1007",
     "coord": [ -73.856077, 40.848447 ],
     "street": "Morris Park Ave",
     "zipcode": "10462"
  },
  "borough": "Bronx",
  "cuisine": "Bakery",
  "grades": [
     { "date": { "$date": 1393804800000 }, "grade": "A", "score": 2 },
     { "date": { "$date": 1378857600000 }, "grade": "A", "score": 6 },
     { "date": { "$date": 1358985600000 }, "grade": "A", "score": 10 },
     { "date": { "$date": 1322006400000 }, "grade": "A", "score": 9 },
     { "date": { "$date": 1299715200000 }, "grade": "B", "score": 14 }
  ],
  "name": "Morris Park Bake Shop",
  "restaurant_id": "30075445"
}

Query:

db.restaurants.aggregate([
{ $unwind: "$grades" },
{ $group: {
    _id: "$name",
totalScore: { $sum: "$grades.score" }
  }},
{ $sort: { totalScore: -1 } },
{ $group: {
    _id: "$totalScore",
restaurants: { $push: "$_id" }
  }},
{ $sort: { _id: -1 } },
{ $limit: 1 },
{ $unwind: "$restaurants" },
{ $group: {
    _id: "$_id",
restaurants: { $push: "$restaurants" }
  }}
])

Output:

[ { _id: 3470, restaurants: [ "Mcdonald'S" ] } ]

Explanation:

The said query in MongoDB that retrieves the restaurants with the name and highest total score.

The $unwind deconstruct the grades array in each restaurant document so that each grade can be processed separately.

The $group groups the documents by name and calculates the sum of all score values in the grades array for each group, resulting in a totalScore field.

The $sort sorts the documents in descending order by totalScore.

The $group again groups the documents by totalScore and creates an array of restaurant names for each group using the $push operator.

The $sort again sorts the documents in descending order by _id (which is the totalScore).

The $unwind again deconstructs the restaurants array created before so that each restaurant can be processed separately.

The $group once again groups the documents by _id (which is the totalScore) and creates an array of restaurant names for each group using the $push operator.

Note: This output is generated using MongoDB server version 3.6

Improve this sample solution and post your code through Disqus.

Previous: Names, addresses, and cuisine of restaurants with a cuisine that ends with the letter 'y'.
Next: Find all the Chinese restaurants in Brooklyn.