In this blog post, we’ll explore how to perform basic CRUD operations and queries on a MongoDB database named mydb with a collection called yelp, based on a Yelp dataset. We’ll use MongoDB’s JavaScript-based query syntax to insert records, find top-rated businesses, count reviews containing specific words, retrieve reviews for a business, update a review, and delete a record. Let’s dive in!
1. Inserting Records into the Yelp Collection
To populate the yelp collection, we can use the insertMany method to add multiple documents at once. Below is an example of inserting 10 review documents, each with fields like business_id, date, review_id, stars, text, type, user_id, cool, useful, and funny.
2. Finding the Top 5 Businesses by Average Rating
To find the top 5 businesses with the highest average rating, we use MongoDB’s aggregation pipeline. The pipeline groups reviews by ‘business_id’, computes the average ‘stars’, sorts in descending order, and limits to 5 results.
For the inserted data, this query returns:
- ‘C3D4E5F6G7H8I9J0K1L2M3’: 4.5
- ‘E5F6G7H8I9J0K1L2M3N4O5’: 4.5
- ‘A1B2C3D4E5F6G7H8I9J0K1’: 4.5
- ‘D4E5F6G7H8I9J0K1L2M3N4’: 4
- ‘B2C3D4E5F6G7H8I9J0K1L2’: 3.5
3. Counting Reviews Containing the Word “Good”
To count reviews with the word ‘good’ in the ‘text’ field, we use countDocuments with a case-insensitive regex.
This returns ‘3’ for the inserted data, as three reviews contain ‘good’ (review IDs ‘R4V5W6X7Y8Z9A0B1C2D3E4’, ‘R6X7Y8Z9A0B1C2D3E4F5G6’, ‘R9A0B1C2D3E4F5G6H7I8J9’).
4. Retrieving All Reviews for a Specific Business
To fetch all reviews for a specific ‘business_id’, we use the find method. For example, to get reviews for ‘9yKzy9PApeiPPOUJEtnvkg’ (from the sample Yelp data):
5. Updating a Review
To update a review, we use updateOne to modify specific fields. For example, to update the review with ‘review_id’ ‘fWKvX83p0-ka4JS3dc6E5A’ (from the sample data, if inserted):
Notes
These queries assume you’re using MongoDB with the ‘yelp’ collection in the ‘mydb’ database.
The ‘date’ field uses MongoDB’s Date object for proper storage. When importing a CSV, ensure dates are parsed correctly (e.g., new Date(“YYYY-MM-DD”)).
The regex in the ‘good’ query is case-insensitive (‘$options: “i”‘).
To import a large ‘yelp.csv’ file, you can use MongoDB’s mongoimport tool or a script to parse and insert the data.
If you encounter issues or need help with specific MongoDB versions, importing data, or running these queries, feel free to share more details!
This setup provides a solid foundation for working with Yelp data in MongoDB. You can extend these queries for more complex analyses, like filtering by date or combining conditions. Happy coding!
