MongoDB Exercise - Find the average score for each borough

Write a MongoDB query to find the average score for each borough.

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: "$borough", avgScore: { $avg: "$grades.score" } } }
])

Output:

{ _id: 'Queens', avgScore: 11.603178953137846 },
{ _id: 'Manhattan', avgScore: 11.375442086648983 },
{ _id: 'Brooklyn', avgScore: 11.492063492063492 },
{ _id: 'Staten Island', avgScore: 11.142215568862275 },
{ _id: 'Bronx', avgScore: 11.30313124583611 }

Explanation:

The said query in MongoDB which groups the documents by borough and calculate the average score for each group, and then sort the results in descending order by the avgScore field.

The $unwind operator is used to flatten the grades array,

and creates a new document for each element in the array. Then, it groups the documents by borough and calculates the average score for each group using the $avg operator on the flattened grades.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 lowest score for each cuisine.
Next: Find the highest score for each borough.