MongoDB Exercise - Names, addresses and grades of restaurants with at least one 'A' and no 'B' and ‘C’ grades

Write a MongoDB query to find the name, address, and grades of the restaurants that have at least one 'A' grade, no 'B' grades, and no 'C' grades.

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: [
{ "grades.grade": "A" },
{ "grades.grade": { $not: { $eq: "B" } } },
{ "grades.grade": { $not: { $eq: "C" } } }
  ]
},
{ name: 1, address: 1, "grades.grade":1, _id: 0 })

Output:

{
address: {
building: '8825',
coord: [ -73.8803827, 40.7643124 ],
street: 'Astoria Boulevard',
zipcode: '11369'
    },
    grades: [ { grade: 'Z' }, { grade: 'A' }, { grade: 'A' }, { grade: 'A' } ],
name: 'Brunos On The Boulevard'
  },
  {
address: {
building: '1839',
coord: [ -73.9482609, 40.6408271 ],
street: 'Nostrand Avenue',
zipcode: '11226'
    },
    grades: [ { grade: 'A' }, { grade: 'A' }, { grade: 'A' }, { grade: 'A' } ],
name: 'Taste The Tropics Ice Cream'
  },
  {
address: {
building: '351',
coord: [ -73.98513559999999, 40.7676919 ],
street: 'West   57 Street',
zipcode: '10019'
    },
    grades: [ { grade: 'A' }, { grade: 'A' }, { grade: 'A' }, { grade: 'A' } ],
name: 'Dj Reynolds Pub And Restaurant'
  },

.....

Explanation:

The said query in MongoDB that finds the name, address, and grades of restaurants that have at least one 'A' grade, no 'B' grades, and no 'C' grades.

The $and operator to combines three conditions, the first one checks for at least one 'A' grade. A dot notation have used to access the grade field within the grades array.

The second and third ones use the $not operator to exclude any documents that have a 'B' or 'C' grade, respectively which are coming to compare by the $eq operator.

The argument of the find() method which specifies which fields to include in the query results and it includes the name, address, and grades.grade fields, but excludes the _id field.

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 grades of restaurants with at least one 'A' and no 'C' grades.
Next: Find out which restaurants have the word "coffee" in their names.