MongoDB Exercise - Find the lowest score for each restaurant

Write a MongoDB query to find the lowest score for each restaurant.

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",
      lowest_score: {
        $min: "$grades.score"
      }
    }
  }
])

Output:

{ _id: 'Kent Theatre', lowest_score: 5 },
{ _id: 'The Courtyard Ale House', lowest_score: 5 },
{ _id: 'Bicheiros Bar', lowest_score: 5 },
{ _id: 'Simpson Thacher And Bartlet Cafe', lowest_score: 2 },
{ _id: "Emilia'S Restaurant", lowest_score: 9 },
{ _id: 'The Brazen Head', lowest_score: 2 },
{ _id: "Vic'S", lowest_score: 9 },
{ _id: 'Gradisca Restaurant', lowest_score: 12 },
{ _id: 'Glorious Food', lowest_score: 9 },
{ _id: "Dominick'S Bar & Restaurant", lowest_score: 6 },


.....

Explanation:

The said query in MongoDB returns a list of documents, each containing the _id field as grouped documents on name field and the lowest_score field as grouped documents on the calculated lowest score field.

The $unwind operator deconstructs the grades array, creating a new document for each element in the array. This is necessary to group the documents by the restaurant name and calculate the lowest score for each group, and it is not possible to do that directly on an array.

The $min operator calculates the lowest score for each group, which will be stored in the lowest_score field.

Note: This output is generated using MongoDB server version 3.6

Improve this sample solution and post your code through Disqus.

Previous: Find the highest score for each restaurant.
Next: Find the count of restaurants in each borough.