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

Write a MongoDB query to find the name ,address and grades of the restaurants that have at least one 'A' grade 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: "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 returns a list of all the restaurant names and addresses and grades that have at least one 'A' grade and no 'C' grades.

The $and operator combines the two conditions into a single query. The $elemMatch operator matches any elements of the grades array that have a grade field equal to "A" and the $not and $eq operators are used together to negate the condition for "C", so that it matches documents where there are no elements with "C".

The argument to the find() method are decide to include the fields name, address and grades, so a value of 1 is included with these fields and used a value of 0 to exclude 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 and addresses of restaurants with at least one 'A' and no 'B' grades.
Next: Names, addresses and grades of restaurants with at least one 'A' and no 'B' and ‘C’ grades.