Writing Queries for MongoDB

MongoDB Logo
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.

Part 1: Wine database¶

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 (Ex: studying for the final exam).
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).
Notice the initial code is db.getCollectionNames();. Press the Run button at the bottom, and copy the query and result into your notes file.
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 }
```
Write the following queries:
- Find all orders for product 212.
  Solution (try before you look!)
```
db.order_lines.find({ product: 212 })
```
- 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¶

Repeat steps 2–4 above for the Restaurants database.
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!)
```
db.restaurants.find(
    { location: "London" },
    { name: 1, rating: 1 }
).sort({ rating: -1 })
```
- 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.

Python Example¶

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()