MongoDB Exercise - Find restaurants in Manhattan or Brooklyn that have at least one grade under 5

Write a MongoDB query to find the restaurants that have at least one grade with a score of less than 5 and that are located in the borough of Manhattan or Brooklyn.

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.find({
  $and: [
    {
      $or: [
        {borough: "Manhattan"},
        {borough: "Brooklyn"}
      ]
    },
    {
      "grades.score": { $lt: 5 }
    }
  ]
})

Output:

{ "_id" : ObjectId("6422c0161238e3bec47ca601"), "address" : { "building" : "351", "coord" : [ -73.98513559999999, 40.7676919 ], "street" : "West   57 Street", "zipcode" : "10019" }, "borough" : "Manhattan", "cuisine" : "Irish", "grades" : [ { "date" : { "$date" : 1409961600000 }, "grade" : "A", "score" : 2 }, { "date" : { "$date" : 1374451200000 }, "grade" : "A", "score" : 11 }, { "date" : { "$date" : 1343692800000 }, "grade" : "A", "score" : 12 }, { "date" : { "$date" : 1325116800000 }, "grade" : "A", "score" : 12 } ], "name" : "Dj Reynolds Pub And Restaurant", "restaurant_id" : "30191841" }
{ "_id" : ObjectId("6422c0181238e3bec47ca607"), "address" : { "building" : "6409", "coord" : [ -74.00528899999999, 40.628886 ], "street" : "11 Avenue", "zipcode" : "11219" }, "borough" : "Brooklyn", "cuisine" : "American ", "grades" : [ { "date" : { "$date" : 1405641600000 }, "grade" : "A", "score" : 12 }, { "date" : { "$date" : 1375142400000 }, "grade" : "A", "score" : 12 }, { "date" : { "$date" : 1360713600000 }, "grade" : "A", "score" : 11 }, { "date" : { "$date" : 1345075200000 }, "grade" : "A", "score" : 2 }, { "date" : { "$date" : 1313539200000 }, "grade" : "A", "score" : 11 } ], "name" : "Regina Caterers", "restaurant_id" : "40356649" }
.....

Explanation:

The given query in MongoDB that retrieves all documents from the 'restaurants' collection located in the "borough" Manhattan or Brooklyn that have received a score of less than 5 in any of their grades.

The $or operator is used to match documents that have the "borough" field equal to "Manhattan" or "Brooklyn" and the other condition that matches documents that have at least one element in the "grades" array where the "score" field is less than 5.

Note: This output is generated using MongoDB server version 3.6

Improve this sample solution and post your code through Disqus.

Previous: Find the restaurants in Manhattan that have at least one grade under 5.
Next: Restaurants that are not American and have at least one low grade are located in Manhattan or Brooklyn.