Writing Queries for MongoDB

Image source: Wikimedia Commons

Background

The authors of the textbook Principles of Database Management provide a playground for trying out MySQL, MongoDB, Neo4j, and other database systems. In today's lab, you will practice writing queries for MongoDB in the playground environment.

Instructions

Create a file named mongo-notes.js for taking notes during the lab. During each step below, copy both the query and the result into your notes file for future reference. Submit your notes to Gradescope.

Data Files

If you would like to see the entire database that was created in the Playground, see wine.mongo and restaurants.mongo.

Part 1: Wine database

1. Write your name in a comment at the top of your mongo-notes.js file.

2. Open the PDBM Playground website and click the Wine database button under Try MongoDB. If the editor font is too small, press Ctrl++ to Zoom in (or Cmd++ on Mac).

3. Notice the initial code is db.getCollectionNames();. Press the Run button at the bottom, and copy the query and result into your notes file.

4. For each of the five collections, run the find() method. Copy one document for each collection into your notes, so you have a sense of the database structure. For example, db.order_lines.find(); returns documents in this format:

   { "_id" : ObjectId("67360e68edadeabdf8d8751a"), "product_order" : 1511, "product" : 212, "quantity" : 2 }
   

5. Copy the the following queries as comments in your notes file. Write code for each query and paste the results under the code.

   • Find all orders for product 212.

     Solution (try before you look!)

     // Q1. Find all orders for product 212.
     db.order_lines.find({ product: 212 })
     
     { "_id" : ObjectId("67360e68edadeabdf8d8751a"), "product_order" : 1511, "product" : 212, "quantity" : 2 }
     { "_id" : ObjectId("67360e68edadeabdf8d87532"), "product_order" : 1538, "product" : 212, "quantity" : 15 }
     { "_id" : ObjectId("67360e68edadeabdf8d87536"), "product_order" : 1577, "product" : 212, "quantity" : 6 }
     

   • Find all products with less than 20 available quantity.

   • Find all suppliers in New York.

   • What rose and white wines have more than 50 quantity available?

Part 2: Restaurants database

6. Repeat steps 2–4 above for the Restaurants database.

7. Write the following queries:

   • Find all restaurants in London. Show only the id, name, and rating. Order by rating descending.

     Solution (try before you look!)

     // Q1. Find all restaurants in London. Show only the id, name, and rating. Order by rating descending.
     db.restaurants.find(
         { location: "London" },
         { name: 1, rating: 1 }
     ).sort({ rating: -1 });
     
     { "_id" : 55, "name" : "Alasia", "rating" : "Not yet rated" }
     { "_id" : 101, "name" : "Anokha Indian Bar & Restaurant", "rating" : "Not yet rated" }
     { "_id" : 15, "name" : "Aarthi", "rating" : 6 }
     { "_id" : 165, "name" : "Bamboo Box", "rating" : 5.5 }
     { "_id" : 174, "name" : "Barbican Tandoori", "rating" : 5.5 }
     

   • Find people who have given a rating of 5 to any restaurant. Show only the name and restaurant ID. Order by name ascending.

   • Find all restaurants containing "Thai" in the type of food. Hint: Use { $regex: "Thai", $options: "i" }. Return the name and address of the restaurant.

Submission

Upload your mongo-notes.js file to Gradescope. Your file must have at least 2500 chars, at least 60 lines, and at least 8 comments (one for each query plus your name at the top).

Python Example

We don't have time to install and run MongoDB locally, but this is what the code would look like. The pymongo library is used to interact with MongoDB.

pip install pymongo

The following code executes a query for the Restaurants database. Notice that the syntax for writing queries is very similar to the JavaScript syntax used by the online playground.

from pymongo import MongoClient

# Connect to the database server
client = MongoClient("mongodb://localhost:27017/")
db = client.Restaurants

# Execute the query with sorting
results = db.restaurants.find(
    {"location": "London"},
    {"name": 1, "rating": 1}
).sort("rating", -1)

# Print the results
for restaurant in results:
    print(restaurant)

# Close the MongoDB connection
client.close()