MongoDB Exercise - Find the restaurants that do not prepare any cuisine of American and their grade score more than 70 and lattitude less than a specified number

Last update on May 15 2023 13:07:01 (UTC/GMT +8 hours)

Write a MongoDB query to find the restaurants that do not prepare any cuisine of 'American' and their grade score more than 70 and lattitude less than -65.754168.

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:
                    [
                       {"cuisine" : {$ne :"American "}},
                       {"grades.score" : {$gt : 70}},
                       {"address.coord" : {$lt : -65.754168}}
                    ]
                }
                    );

Output:

{ "_id" : ObjectId("564c2d939eb21ad392f177c8"), "address" : { "building" : "345", "coord" : [ -73.9864626, 40.7266739 ], "street" : "East 6 Street", "zipcode" : "10003" }, "borough" : "Manhattan", "cuisine" : "Indian", "grades" : [ { "date" : ISODate("2014-09-15T00:00:00Z"), "grade" : "A", "score" : 5 }, { "date" : ISODate("2014-01-14T00:00:00Z"), "grade" : "A", "score" : 8 }, { "date" : ISODate("2013-05-30T00:00:00Z"), "grade" : "A", "score" : 12 }, { "date" : ISODate("2013-04-24T00:00:00Z"), "
grade" : "P", "score" : 2 }, { "date" : ISODate("2012-10-01T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2012-04-06T00:00:00Z"), "grade" : "C", "score" : 92 }, { "date" : ISODate("2011-11-03T00:00:00Z"), "grade" : "C", "score" : 41 } ], "name" : "Gandhi", "restaurant_id" : "40381295" }
{ "_id" : ObjectId("564c2d939eb21ad392f17929"), "address" : { "building" : "130", "coord" : [ -73.984758, 40.7457939 ], "street" : "Madison Avenue", "zipcode" : "10016" }, "borough" : "Manhattan", "cuisine" : "Pizza/Italian", "grades" : [ { "date" : ISODate("2014-12-24T00:00:00Z"), "grade" : "Z", "score" : 31 }, { "date" : ISODate("2014-06-17T00:00:00Z"), "grade" : "C", "score" : 98 }, { "date" : ISODate("2013-12-12T00:00:00Z"), "grade" : "C", "score" : 32 }, { "date" : ISODate("2013-05-22T00:00
:00Z"), "grade" : "B", "score" : 21 }, { "date" : ISODate("2012-05-02T00:00:00Z"), "grade" : "A", "score" : 11 } ], "name" : "Bella Napoli", "restaurant_id" : "40393488" }
{ "_id" : ObjectId("564c2d939eb21ad392f17935"), "address" : { "building" : "101", "coord" : [ -73.9243061, 40.8276297 ], "street" : "East 161 Street", "zipcode" : "10451" }, "borough" : "Bronx", "cuisine" : "Latin (Cuban, Dominican, Puerto Rican, South & Central American)", "grades" : [ { "date" : ISODate("2014-04-10T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2013-10-01T00:00:00Z"), "grade" : "A", "score" : 6 }, { "date" : ISODate("2013-04-11T00:00:00Z"), "grade" : "B", "sco
re" : 25 }, { "date" : ISODate("2012-10-25T00:00:00Z"), "grade" : "A", "score" : 12 }, { "date" : ISODate("2012-10-10T00:00:00Z"), "grade" : "P", "score" : 12 }, { "date" : ISODate("2012-05-25T00:00:00Z"), "grade" : "B", "score" : 14 }, { "date" : ISODate("2011-09-14T00:00:00Z"), "grade" : "B", "score" : 26 }, { "date" : ISODate("2011-04-25T00:00:00Z"), "grade" : "C", "score" : 76 } ], "name" : "El Molino Rojo Restaurant", "restaurant_id" : "40393688" }
{ "_id" : ObjectId("564c2d949eb21ad392f17a86"), "address" : { "building" : "289", "coord" : [ -73.94610279999999, 40.7137587 ], "street" : "Manhattan Avenue", "zipcode" : "11211" }, "borough" : "Brooklyn", "cuisine" : "Bakery", "grades" : [ { "date" : ISODate("2014-03-19T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2013-10-10T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2013-05-07T00:00:00Z"), "grade" : "A", "score" : 13 }, { "date" : ISODate("2012-09-11T00:
00:00Z"), "grade" : "B", "score" : 18 }, { "date" : ISODate("2012-04-18T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2011-09-27T00:00:00Z"), "grade" : "A", "score" : 8 }, { "date" : ISODate("2011-05-03T00:00:00Z"), "grade" : "C", "score" : 77 } ], "name" : "Fortunato Bros Cafe & Bakery", "restaurant_id" : "40400561" }
{ "_id" : ObjectId("564c2d949eb21ad392f180b0"), "address" : { "building" : "231", "coord" : [ -73.9772294, 40.7527262 ], "street" : "Grand Central Station", "zipcode" : "10017" }, "borough" : "Manhattan", "cuisine" : "Italian", "grades" : [ { "date" : ISODate("2015-01-07T00:00:00Z"), "grade" : "Z", "score" : 20 }, { "date" : ISODate("2014-07-03T00:00:00Z"), "grade" : "B", "score" : 14 }, { "date" : ISODate("2013-12-21T00:00:00Z"), "grade" : "A", "score" : 13 }, { "date" : ISODate("2013-05-17T00:
00:00Z"), "grade" : "C", "score" : 76 }, { "date" : ISODate("2012-04-20T00:00:00Z"), "grade" : "A", "score" : 12 } ], "name" : "Two Boots Grand Central", "restaurant_id" : "40725591" }
{ "_id" : ObjectId("564c2d949eb21ad392f186d3"), "address" : { "building" : "7705", "coord" : [ -73.8878704, 40.7435875 ], "street" : "Woodside Avenue", "zipcode" : "11373" }, "borough" : "Queens", "cuisine" : "Thai", "grades" : [ { "date" : ISODate("2014-05-12T00:00:00Z"), "grade" : "B", "score" : 14 }, { "date" : ISODate("2013-11-20T00:00:00Z"), "grade" : "C", "score" : 84 }, { "date" : ISODate("2012-09-18T00:00:00Z"), "grade" : "A", "score" : 11 }, { "date" : ISODate("2012-05-03T00:00:00Z"), "
grade" : "B", "score" : 23 } ], "name" : "Spicy Shallot", "restaurant_id" : "40979431" }
{ "_id" : ObjectId("564c2d949eb21ad392f1890c"), "address" : { "building" : "566", "coord" : [ -73.987977, 40.75519500000001 ], "street" : "7 Avenue", "zipcode" : "10018" }, "borough" : "Manhattan", "cuisine" : "Chinese", "grades" : [ { "date" : ISODate("2014-09-23T00:00:00Z"), "grade" : "A", "score" : 7 }, { "date" : ISODate("2013-11-20T00:00:00Z"), "grade" : "B", "score" : 23 }, { "date" : ISODate("2013-04-29T00:00:00Z"), "grade" : "C", "score" : 77 }, { "date" : ISODate("2012-10-23T00:00:00Z")
, "grade" : "B", "score" : 21 }, { "date" : ISODate("2012-05-14T00:00:00Z"), "grade" : "B", "score" : 21 }, { "date" : ISODate("2011-10-26T00:00:00Z"), "grade" : "B", "score" : 23 } ], "name" : "Midtown Buffet", "restaurant_id" : "41038679" }
{ "_id" : ObjectId("564c2d949eb21ad392f18c36"), "address" : { "building" : "181", "coord" : [ -73.97757399999999, 40.67127 ], "street" : "7 Avenue", "zipcode" : "11215" }, "borough" : "Brooklyn", "cuisine" : "Pizza", "grades" : [ { "date" : ISODate("2014-09-23T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2014-04-16T00:00:00Z"), "grade" : "B", "score" : 27 }, { "date" : ISODate("2013-08-22T00:00:00Z"), "grade" : "A", "score" : 12 }, { "date" : ISODate("2012-12-27T00:00:00Z"), "
grade" : "B", "score" : 22 }, { "date" : ISODate("2012-06-22T00:00:00Z"), "grade" : "C", "score" : 77 }, { "date" : ISODate("2011-11-22T00:00:00Z"), "grade" : "A", "score" : 12 } ], "name" : "Pino'S La Forchetta", "restaurant_id" : "41129343" }
{ "_id" : ObjectId("564c2d949eb21ad392f18da1"), "address" : { "building" : "499", "coord" : [ -73.9651337, 40.6937213 ], "street" : "Myrtle Avenue", "zipcode" : "11205" }, "borough" : "Brooklyn", "cuisine" : "Mexican", "grades" : [ { "date" : ISODate("2015-01-14T00:00:00Z"), "grade" : "Z", "score" : 17 }, { "date" : ISODate("2014-05-08T00:00:00Z"), "grade" : "C", "score" : 73 }, { "date" : ISODate("2013-04-01T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2012-08-24T00:00:00Z"),
 "grade" : "B", "score" : 25 }, { "date" : ISODate("2012-02-29T00:00:00Z"), "grade" : "A", "score" : 8 } ], "name" : "Los Pollitos Iii", "restaurant_id" : "41163308" }
{ "_id" : ObjectId("564c2d949eb21ad392f19172"), "address" : { "building" : "366", "coord" : [ -73.981843, 40.741207 ], "street" : "3 Avenue", "zipcode" : "10016" }, "borough" : "Manhattan", "cuisine" : "Japanese", "grades" : [ { "date" : ISODate("2014-02-27T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2013-09-10T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2013-03-08T00:00:00Z"), "grade" : "A", "score" : 13 }, { "date" : ISODate("2012-08-13T00:00:00Z"), "gra
de" : "B", "score" : 26 }, { "date" : ISODate("2012-02-02T00:00:00Z"), "grade" : "B", "score" : 18 }, { "date" : ISODate("2011-09-28T00:00:00Z"), "grade" : "C", "score" : 76 }, { "date" : ISODate("2011-05-12T00:00:00Z"), "grade" : "A", "score" : 12 } ], "name" : "East Japanese Restaurant", "restaurant_id" : "41242254" }
{ "_id" : ObjectId("564c2d949eb21ad392f19400"), "address" : { "building" : "104-11", "coord" : [ -73.861317, 40.7502198 ], "street" : "Roosevelt Avenue", "zipcode" : "11368" }, "borough" : "Queens", "cuisine" : "Latin (Cuban, Dominican, Puerto Rican, South & Central American)", "grades" : [ { "date" : ISODate("2014-08-22T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2014-03-10T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2013-09-26T00:00:00Z"), "grade" : "A", "
score" : 11 }, { "date" : ISODate("2013-03-20T00:00:00Z"), "grade" : "C", "score" : 73 }, { "date" : ISODate("2012-09-24T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2012-04-18T00:00:00Z"), "grade" : "B", "score" : 21 } ], "name" : "Los Mismo Restaurant", "restaurant_id" : "41288811" }
{ "_id" : ObjectId("564c2d949eb21ad392f1948d"), "address" : { "building" : "3845", "coord" : [ -73.94352959999999, 40.8361392 ], "street" : "Broadway", "zipcode" : "10032" }, "borough" : "Manhattan", "cuisine" : "Seafood", "grades" : [ { "date" : ISODate("2014-09-18T00:00:00Z"), "grade" : "Z", "score" : 11 }, { "date" : ISODate("2014-01-10T00:00:00Z"), "grade" : "B", "score" : 27 }, { "date" : ISODate("2013-07-05T00:00:00Z"), "grade" : "C", "score" : 5 }, { "date" : ISODate("2013-01-16T00:00:00Z
"), "grade" : "A", "score" : 12 }, { "date" : ISODate("2012-07-17T00:00:00Z"), "grade" : "B", "score" : 26 }, { "date" : ISODate("2012-03-14T00:00:00Z"), "grade" : "C", "score" : 77 } ], "name" : "Brothers Fish Market", "restaurant_id" : "41297769" }
{ "_id" : ObjectId("564c2d949eb21ad392f19c8b"), "address" : { "building" : "222", "coord" : [ -73.9579033, 40.7332745 ], "street" : "Franklin Street", "zipcode" : "11222" }, "borough" : "Brooklyn", "cuisine" : "Italian", "grades" : [ { "date" : ISODate("2014-12-11T00:00:00Z"), "grade" : "Z", "score" : 18 }, { "date" : ISODate("2013-10-31T00:00:00Z"), "grade" : "A", "score" : 12 }, { "date" : ISODate("2013-03-20T00:00:00Z"), "grade" : "B", "score" : 24 }, { "date" : ISODate("2012-07-18T00:00:00Z"
), "grade" : "B", "score" : 18 }, { "date" : ISODate("2012-01-12T00:00:00Z"), "grade" : "C", "score" : 81 } ], "name" : "Anella", "restaurant_id" : "41410058" }
{ "_id" : ObjectId("564c2d949eb21ad392f1a8b9"), "address" : { "building" : "29", "coord" : [ -73.9087571, 40.8539355 ], "street" : "West Burnside Avenue", "zipcode" : "10453" }, "borough" : "Bronx", "cuisine" : "Chinese", "grades" : [ { "date" : ISODate("2014-10-21T00:00:00Z"), "grade" : "B", "score" : 15 }, { "date" : ISODate("2014-03-05T00:00:00Z"), "grade" : "A", "score" : 7 }, { "date" : ISODate("2013-07-24T00:00:00Z"), "grade" : "A", "score" : 6 }, { "date" : ISODate("2013-02-12T00:00:00Z")
, "grade" : "A", "score" : 12 }, { "date" : ISODate("2012-07-24T00:00:00Z"), "grade" : "C", "score" : 73 }, { "date" : ISODate("2012-02-28T00:00:00Z"), "grade" : "B", "score" : 23 } ], "name" : "Ming Wong Restaurant", "restaurant_id" : "41563476" }
{ "_id" : ObjectId("564c2d949eb21ad392f1a951"), "address" : { "building" : "1724", "coord" : [ -73.94981, 40.780043 ], "street" : "2 Avenue", "zipcode" : "10128" }, "borough" : "Manhattan", "cuisine" : "Indian", "grades" : [ { "date" : ISODate("2014-09-25T00:00:00Z"), "grade" : "A", "score" : 7 }, { "date" : ISODate("2014-03-20T00:00:00Z"), "grade" : "A", "score" : 12 }, { "date" : ISODate("2013-09-09T00:00:00Z"), "grade" : "B", "score" : 21 }, { "date" : ISODate("2013-03-25T00:00:00Z"), "grade"
 : "B", "score" : 18 }, { "date" : ISODate("2012-08-15T00:00:00Z"), "grade" : "A", "score" : 11 }, { "date" : ISODate("2011-12-23T00:00:00Z"), "grade" : "C", "score" : 98 } ], "name" : "Baluchi'S Indian Food", "restaurant_id" : "41569277" }
{ "_id" : ObjectId("564c2d949eb21ad392f1abd8"), "address" : { "building" : "844", "coord" : [ -73.970671, 40.7515735 ], "street" : "2 Avenue", "zipcode" : "10017" }, "borough" : "Manhattan", "cuisine" : "Pizza", "grades" : [ { "date" : ISODate("2014-07-15T00:00:00Z"), "grade" : "A", "score" : 12 }, { "date" : ISODate("2013-07-15T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2013-02-19T00:00:00Z"), "grade" : "A", "score" : 13 }, { "date" : ISODate("2012-08-02T00:00:00Z"), "grade
" : "A", "score" : 11 }, { "date" : ISODate("2012-02-29T00:00:00Z"), "grade" : "B", "score" : 14 }, { "date" : ISODate("2011-09-02T00:00:00Z"), "grade" : "C", "score" : 79 } ], "name" : "La Trattoria", "restaurant_id" : "41595287" }
{ "_id" : ObjectId("564c2d949eb21ad392f1ad35"), "address" : { "building" : "194-03", "coord" : [ -73.7874367, 40.7581296 ], "street" : "Northern Boulevard", "zipcode" : "11358" }, "borough" : "Queens", "cuisine" : "Korean", "grades" : [ { "date" : ISODate("2014-09-16T00:00:00Z"), "grade" : "C", "score" : 78 }, { "date" : ISODate("2014-01-09T00:00:00Z"), "grade" : "B", "score" : 23 }, { "date" : ISODate("2013-05-22T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2012-11-27T00:00:00
Z"), "grade" : "A", "score" : 12 }, { "date" : ISODate("2012-05-16T00:00:00Z"), "grade" : "B", "score" : 27 } ], "name" : "Gal Bi Ma Eul", "restaurant_id" : "41608854" }
{ "_id" : ObjectId("564c2d949eb21ad392f1adde"), "address" : { "building" : "56", "coord" : [ -73.99836309999999, 40.7158265 ], "street" : "Mott Street", "zipcode" : "10013" }, "borough" : "Manhattan", "cuisine" : "Chinese", "grades" : [ { "date" : ISODate("2014-11-18T00:00:00Z"), "grade" : "Z", "score" : 25 }, { "date" : ISODate("2014-03-31T00:00:00Z"), "grade" : "B", "score" : 22 }, { "date" : ISODate("2013-07-12T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2013-02-08T00:00:00
Z"), "grade" : "C", "score" : 73 }, { "date" : ISODate("2011-12-07T00:00:00Z"), "grade" : "A", "score" : 10 } ], "name" : "Wonton Noodle Garden", "restaurant_id" : "41614664" }
{ "_id" : ObjectId("564c2d949eb21ad392f1b47e"), "address" : { "building" : "1191", "coord" : [ -73.953931, 40.68096 ], "street" : "Fulton Street", "zipcode" : "11216" }, "borough" : "Brooklyn", "cuisine" : "African", "grades" : [ { "date" : ISODate("2015-01-07T00:00:00Z"), "grade" : "Z", "score" : 38 }, { "date" : ISODate("2014-06-26T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2013-12-26T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2013-12-02T00:00:00Z"), "gr
ade" : "P", "score" : 4 }, { "date" : ISODate("2013-06-13T00:00:00Z"), "grade" : "B", "score" : 16 }, { "date" : ISODate("2013-01-03T00:00:00Z"), "grade" : "C", "score" : 58 }, { "date" : ISODate("2012-05-24T00:00:00Z"), "grade" : "C", "score" : 78 } ], "name" : "Cheikh Umar Futiyu Restaurant", "restaurant_id" : "41660581" }
{ "_id" : ObjectId("564c2d949eb21ad392f1b688"), "address" : { "building" : "43-46", "coord" : [ -73.9224579, 40.7441205 ], "street" : "42 Street", "zipcode" : "11104" }, "borough" : "Queens", "cuisine" : "Japanese", "grades" : [ { "date" : ISODate("2014-07-31T00:00:00Z"), "grade" : "B", "score" : 22 }, { "date" : ISODate("2014-01-02T00:00:00Z"), "grade" : "A", "score" : 12 }, { "date" : ISODate("2013-06-05T00:00:00Z"), "grade" : "B", "score" : 14 }, { "date" : ISODate("2012-09-14T00:00:00Z"), "g
rade" : "C", "score" : 73 } ], "name" : "Takesushi", "restaurant_id" : "41679611" }
Type "it" for more

Note: This output is generated using MongoDB server version 3.6

Explanation:

The given shell command in MongoDB query that finds all documents in the 'restaurants' collection that looking for restaurants that serve a cuisine other than American, have a score greater than 70, and are located to the west of -65.754168.
The {$and: [...]} is a logical operator that combines multiple conditions.
The {"cuisine" : {$ne :"American "}} is a query filters the documents where the value of the "cuisine" field is not equal to "American ".
The {"grades.score" : {$gt : 70}} filters that the documents where there is at least one element in the "grades" array where the value of the "score" field is greater than 70 and the {"address.coord" : {$lt : -65.754168}} filters the documents where the value of the "coord" field inside the "address" subdocument is less than -65.754168.

Improve this sample solution and post your code through Disqus.

Previous: Find the restaurants which locate in a latitude value less than -95.754168..
Next: Restaurants without any American cuisine that have a score of 70 or more and are located less than -65.754168 longitude.