🎓 MongoDB Mastery: From Basics to Advanced

// Retrieve all restaurants
db.restaurants.find({});

// Same as above (empty filter is default)
db.restaurants.find();

// Limit to first 10 for readability
db.restaurants.find({}).limit(10);

// Pretty print for better formatting
db.restaurants.find({}).limit(5).pretty();

// Get all movies
db.movies.find({});

// Count total movies
db.movies.countDocuments({});
// Output: 500+

// Get first 5 movies
db.movies.find({}).limit(5);

// Get all books
db.books.find({});

// Get all authors
db.authors.find({});

// Get all borrowers
db.borrowers.find({});

// Get all loan records
db.loans.find({});

// Store cursor in a variable
var cursor = db.restaurants.find({});

// Check if cursor has more documents
cursor.hasNext();
// Output: true

// Get next document
cursor.next();

// Iterate through all documents
var cursor = db.restaurants.find({}).limit(5);
while(cursor.hasNext()) {
  printjson(cursor.next());
}

// forEach iteration
db.restaurants.find({}).limit(5).forEach(function(doc) {
  print(doc.name);
});

// Count all restaurants
db.restaurants.countDocuments({});
// Output: 3772

// Alternative (deprecated but still works)
db.restaurants.count();
// Output: 3772

// Estimated count (faster for large collections)
db.restaurants.estimatedDocumentCount();
// Output: ~3772

// Implicit equality (most common)
db.restaurants.find({ name: "Wendy'S" });

// Explicit $eq operator (same result)
db.restaurants.find({ name: { $eq: "Wendy'S" } });

// Count matches
db.restaurants.countDocuments({ name: "Wendy'S" });
// Output: Multiple Wendy's locations

// Find Manhattan restaurants
db.restaurants.find({ borough: "Manhattan" }).limit(10);

// Count Manhattan restaurants
db.restaurants.countDocuments({ borough: "Manhattan" });
// Output: 1883

// Get unique boroughs
db.restaurants.distinct("borough");
// Output: ["Bronx", "Brooklyn", "Manhattan", "Queens", "Staten Island"]

// Find movies from 1994
db.movies.find({ year: 1994 });

// Count 1994 movies
db.movies.countDocuments({ year: 1994 });

// Find by director
db.movies.find({ director: "Christopher Nolan" });

// Find by rating
db.movies.find({ rated: "PG-13" });

// Find book by ISBN
db.books.findOne({ isbn: "978-0-06-112008-4" });

// Find all books published in 1960
db.books.find({ published_year: 1960 });

// Find books by author (using author_id)
db.books.find({ author_id: ObjectId("...") });

// Dot notation for nested fields
db.restaurants.find({ "address.zipcode": "10462" });

// Find by building number
db.restaurants.find({ "address.building": "1007" });

// Find by street
db.restaurants.find({ "address.street": "Morris Park Ave" });

// Find restaurants in specific zipcode
db.restaurants.find({ "address.zipcode": "10462" });

// Find by building number
db.restaurants.find({ "address.building": "1007" });

// Find by street name
db.restaurants.find({ "address.street": "Morris Park Ave" });

// Combine multiple nested fields
db.restaurants.find({
  "address.zipcode": "10462",
  "address.street": "Morris Park Ave"
});

// Find restaurants with grade "A" (searches all array elements)
db.restaurants.find({ "grades.grade": "A" });

// Find restaurants with score less than 10
db.restaurants.find({ "grades.score": { $lt: 10 } });

// Find restaurants inspected after specific date
db.restaurants.find({
  "grades.date": { $gt: ISODate("2014-01-01") }
});

// Combine conditions in array elements
db.restaurants.find({
  "grades.grade": "A",
  "grades.score": { $lt: 5 }
});

// Find highly rated movies on IMDB
db.movies.find({ "imdb.rating": { $gte: 8.5 } });

// Find movies with many IMDB votes
db.movies.find({ "imdb.votes": { $gte: 1000000 } });

// Find by IMDB ID
db.movies.find({ "imdb.id": "tt1375666" });

// Multi-level nesting: viewer ratings
db.movies.find({ "tomatoes.viewer.rating": { $gte: 4.0 } });

// Critic score above threshold
db.movies.find({ "tomatoes.critic.rating": { $gte: 80 } });

// Combine IMDB and Tomatoes criteria
db.movies.find({
  "imdb.rating": { $gte: 8.0 },
  "tomatoes.critic.rating": { $gte: 75 }
});

// Find books by specific publisher
db.books.find({ "publisher.name": "HarperCollins" });

// Find books published in New York
db.books.find({ "publisher.location": "New York" });

// Find books by publication year
db.books.find({ "publisher.year": { $gte: 1950, $lte: 1970 } });

// Query book metadata - page count
db.books.find({ "metadata.pages": { $gte: 300 } });

// Find by language
db.books.find({ "metadata.language": "English" });

// Find by ISBN in metadata
db.books.find({ "metadata.isbn": "978-0-06-112008-4" });

// Exact match - ALL fields must match in EXACT order
db.restaurants.find({
  "address": {
    "building": "1007",
    "coord": [-73.856077, 40.848447],
    "street": "Morris Park Ave",
    "zipcode": "10462"
  }
});

// ❌ This will NOT match if field order differs
db.restaurants.find({
  "address": {
    "zipcode": "10462",  // Wrong order!
    "building": "1007",
    "street": "Morris Park Ave",
    "coord": [-73.856077, 40.848447]
  }
});

// ✅ Better: Use dot notation for flexible matching
db.restaurants.find({
  "address.building": "1007",
  "address.zipcode": "10462"
});

// Find restaurants with score > 30 in most recent inspection
db.restaurants.find({
  "grades.0.score": { $gt: 30 }
});

// Count them
db.restaurants.countDocuments({ "grades.0.score": { $gt: 30 } });

// Show only name and score
db.restaurants.find(
  { "grades.0.score": { $gt: 30 } },
  { name: 1, "grades.0.score": 1, _id: 0 }
).limit(5);

// Find movies after 2010
db.movies.find({ year: { $gt: 2010 } });

// Count modern movies
db.movies.countDocuments({ year: { $gt: 2010 } });

// Find recent action movies
db.movies.find({
  year: { $gt: 2010 },
  genres: "Action"
});

// Find highly-rated movies (rating > 8.5)
db.movies.find({ "imdb.rating": { $gt: 8.5 } });

// Sort by rating descending
db.movies.find({ "imdb.rating": { $gt: 8.5 } })
  .sort({ "imdb.rating": -1 })
  .limit(10);

// Show title and rating only
db.movies.find(
  { "imdb.rating": { $gt: 8.5 } },
  { title: 1, "imdb.rating": 1, year: 1, _id: 0 }
);

// Find books published before 1950
db.books.find({ published_year: { $lt: 1950 } });

// Count classic books
db.books.countDocuments({ published_year: { $lt: 1950 } });

// Find classics with available copies
db.books.find({
  published_year: { $lt: 1950 },
  available_copies: { $gt: 0 }
});

// Find restaurants with excellent scores (< 5)
db.restaurants.find({ "grades.0.score": { $lt: 5 } });

// Find excellent Italian restaurants in Manhattan
db.restaurants.find({
  "grades.0.score": { $lt: 5 },
  cuisine: "Italian",
  borough: "Manhattan"
}).limit(10);

// Find 90s movies using range
db.movies.find({
  year: { $gte: 1990, $lte: 1999 }
});

// Count 90s movies
db.movies.countDocuments({
  year: { $gte: 1990, $lte: 1999 }
});

// Find 90s action movies with good ratings
db.movies.find({
  year: { $gte: 1990, $lte: 1999 },
  genres: "Action",
  "imdb.rating": { $gte: 7.0 }
}).sort({ "imdb.rating": -1 });

// Find restaurants with moderate scores (10-20)
db.restaurants.find({
  "grades.0.score": { $gte: 10, $lte: 20 }
});

// By cuisine and borough
db.restaurants.find({
  "grades.0.score": { $gte: 10, $lte: 20 },
  cuisine: "Chinese",
  borough: "Queens"
});

// Find mid-20th century books
db.books.find({
  published_year: { $gte: 1950, $lte: 2000 }
});

// By genre
db.books.find({
  published_year: { $gte: 1950, $lte: 2000 },
  genre: "Science Fiction"
});

// Find non-Manhattan restaurants
db.restaurants.find({ borough: { $ne: "Manhattan" } });

// Count them
db.restaurants.countDocuments({ borough: { $ne: "Manhattan" } });

// Find Italian food outside Manhattan
db.restaurants.find({
  borough: { $ne: "Manhattan" },
  cuisine: "Italian"
});

// Exclude PG-13 movies
db.movies.find({ rated: { $ne: "PG-13" } });

// Find R-rated or unrated
db.movies.find({
  rated: { $ne: "PG-13" },
  year: { $gte: 2000 }
});

// Find restaurants in multiple boroughs
db.restaurants.find({
  borough: { $in: ["Manhattan", "Brooklyn", "Queens"] }
});

// Count them
db.restaurants.countDocuments({
  borough: { $in: ["Manhattan", "Brooklyn", "Queens"] }
});

// Find Italian in these boroughs
db.restaurants.find({
  borough: { $in: ["Manhattan", "Brooklyn", "Queens"] },
  cuisine: "Italian"
});

// Find multiple cuisine types
db.restaurants.find({
  cuisine: { $in: ["Italian", "Chinese", "Japanese"] }
});

// In Manhattan only
db.restaurants.find({
  cuisine: { $in: ["Italian", "Chinese", "Japanese"] },
  borough: "Manhattan"
});

// Exclude multiple boroughs
db.restaurants.find({
  borough: { $nin: ["Manhattan", "Bronx"] }
});

// This returns: Brooklyn, Queens, Staten Island
db.restaurants.countDocuments({
  borough: { $nin: ["Manhattan", "Bronx"] }
});

// Find multiple genres
db.movies.find({
  genres: { $in: ["Action", "Thriller", "Sci-Fi"] }
});

// Recent ones with good ratings
db.movies.find({
  genres: { $in: ["Action", "Thriller", "Sci-Fi"] },
  year: { $gte: 2010 },
  "imdb.rating": { $gte: 7.0 }
});

// Implicit AND (listing conditions together)
db.restaurants.find({
  cuisine: "Italian",
  borough: "Manhattan",
  "grades.0.grade": "A"
});

// Explicit $and (same result, verbose)
db.restaurants.find({
  $and: [
    { cuisine: "Italian" },
    { borough: "Manhattan" },
    { "grades.0.grade": "A" }
  ]
});

// When you need multiple conditions on same field
db.restaurants.find({
  $and: [
    { "grades.0.score": { $gt: 10 } },
    { "grades.0.score": { $lt: 30 } }
  ]
});

// More common: combine in one object (preferred)
db.restaurants.find({
  "grades.0.score": { $gt: 10, $lt: 30 }
});

// Find Italian OR Chinese
db.restaurants.find({
  $or: [
    { cuisine: "Italian" },
    { cuisine: "Chinese" }
  ]
});

// Note: For same field, $in is more efficient
db.restaurants.find({
  cuisine: { $in: ["Italian", "Chinese"] }
});

// Manhattan OR excellent score
db.restaurants.find({
  $or: [
    { borough: "Manhattan" },
    { "grades.0.score": { $lt: 5 } }
  ]
});

// Find Italian in Manhattan OR Brooklyn
db.restaurants.find({
  cuisine: "Italian",
  $or: [
    { borough: "Manhattan" },
    { borough: "Brooklyn" }
  ]
});

// Recent OR highly rated
db.movies.find({
  $or: [
    { year: { $gt: 2015 } },
    { "imdb.rating": { $gt: 8.5 } }
  ]
}).sort({ "imdb.rating": -1 });

// Italian AND (Manhattan OR Brooklyn) AND grade A
db.restaurants.find({
  cuisine: "Italian",
  $or: [
    { borough: "Manhattan" },
    { borough: "Brooklyn" }
  ],
  "grades.0.grade": "A"
});

// Alternative using $in (cleaner for same field)
db.restaurants.find({
  cuisine: "Italian",
  borough: { $in: ["Manhattan", "Brooklyn"] },
  "grades.0.grade": "A"
});

// Action AND (90s OR 2000s) AND rating >= 7
db.movies.find({
  genres: "Action",
  $or: [
    { year: { $gte: 1990, $lte: 1999 } },
    { year: { $gte: 2000, $lte: 2009 } }
  ],
  "imdb.rating": { $gte: 7.0 }
}).sort({ "imdb.rating": -1 });

// (Fiction OR Mystery) AND year > 1980 AND available
db.books.find({
  $or: [
    { genre: "Fiction" },
    { genre: "Mystery" }
  ],
  published_year: { $gt: 1980 },
  available_copies: { $gt: 0 }
});

// Get first 10 restaurants
db.restaurants.find().limit(10);

// Get first 5 Italian restaurants
db.restaurants.find({ cuisine: "Italian" }).limit(5);

// Get first 3 Manhattan restaurants with grade A
db.restaurants.find({
  borough: "Manhattan",
  "grades.0.grade": "A"
}).limit(3);

// Top 10 by rating (we'll add sort in next section)
db.movies.find({ "imdb.rating": { $exists: true } })
  .limit(10);

// Top 5 recent movies (after 2015)
db.movies.find({ year: { $gte: 2015 } }).limit(5);

// Sort A-Z (ascending)
db.restaurants.find().sort({ name: 1 }).limit(10);

// Sort Z-A (descending)
db.restaurants.find().sort({ name: -1 }).limit(10);

// Sort Italian restaurants by name
db.restaurants.find({ cuisine: "Italian" })
  .sort({ name: 1 })
  .limit(10);

// Best scores first (ascending - lower is better)
db.restaurants.find()
  .sort({ "grades.0.score": 1 })
  .limit(10);

// Worst scores first (descending)
db.restaurants.find()
  .sort({ "grades.0.score": -1 })
  .limit(10);

// Manhattan restaurants, best scores first
db.restaurants.find({ borough: "Manhattan" })
  .sort({ "grades.0.score": 1 })
  .limit(10);

// Top rated movies (highest rating first)
db.movies.find()
  .sort({ "imdb.rating": -1 })
  .limit(10);

// Top rated action movies
db.movies.find({ genres: "Action" })
  .sort({ "imdb.rating": -1 })
  .limit(10);

// Recent movies, highest rated first
db.movies.find({ year: { $gte: 2015 } })
  .sort({ "imdb.rating": -1 })
  .limit(10);

// Sort by borough (ascending), then name (ascending)
db.restaurants.find()
  .sort({ borough: 1, name: 1 })
  .limit(20);

// Sort by cuisine, then score (best first)
db.restaurants.find()
  .sort({ cuisine: 1, "grades.0.score": 1 })
  .limit(20);

// Italian restaurants: borough, then score
db.restaurants.find({ cuisine: "Italian" })
  .sort({ borough: 1, "grades.0.score": 1 })
  .limit(10);

// Newest first, then highest rated
db.movies.find()
  .sort({ year: -1, "imdb.rating": -1 })
  .limit(20);

// Action movies: year descending, rating descending
db.movies.find({ genres: "Action" })
  .sort({ year: -1, "imdb.rating": -1 })
  .limit(10);

// Oldest books first
db.books.find()
  .sort({ published_year: 1 })
  .limit(10);

// Available books, newest first
db.books.find({ available_copies: { $gt: 0 } })
  .sort({ published_year: -1 })
  .limit(10);

// Fiction books by title A-Z
db.books.find({ genre: "Fiction" })
  .sort({ title: 1 })
  .limit(20);

// Skip first 10, get next 10
db.restaurants.find().skip(10).limit(10);

// Skip first 20, get next 10
db.restaurants.find().skip(20).limit(10);

// Italian restaurants: skip 5, get 5
db.restaurants.find({ cuisine: "Italian" })
  .skip(5)
  .limit(5);

// Page 1: skip 0, limit 10
var page = 1;
var pageSize = 10;
db.movies.find()
  .skip((page - 1) * pageSize)
  .limit(pageSize);

// Page 2: skip 10, limit 10
page = 2;
db.movies.find()
  .skip((page - 1) * pageSize)
  .limit(pageSize);

// Page 3: skip 20, limit 10
page = 3;
db.movies.find()
  .skip((page - 1) * pageSize)
  .limit(pageSize);

// Setup pagination variables
var page = 1;
var pageSize = 20;
var query = { cuisine: "Italian", borough: "Manhattan" };

// Get total count for pagination info
var totalCount = db.restaurants.countDocuments(query);
var totalPages = Math.ceil(totalCount / pageSize);

// Get page 1
db.restaurants.find(query)
  .sort({ name: 1 })
  .skip((page - 1) * pageSize)
  .limit(pageSize);

// Result info
print("Showing page " + page + " of " + totalPages);
print("Total results: " + totalCount);

// Include only specific fields
db.restaurants.find(
  {},
  { name: 1, borough: 1, cuisine: 1 }
).limit(5);

// Same but exclude _id
db.restaurants.find(
  {},
  { name: 1, borough: 1, cuisine: 1, _id: 0 }
).limit(5);

// Italian restaurants: name and address only
db.restaurants.find(
  { cuisine: "Italian" },
  { name: 1, "address.street": 1, _id: 0 }
).limit(10);

// Exclude specific fields
db.restaurants.find(
  {},
  { grades: 0, restaurant_id: 0 }
).limit(5);

// Exclude only _id
db.restaurants.find(
  {},
  { _id: 0 }
).limit(5);

// Project nested fields with dot notation
db.restaurants.find(
  {},
  { name: 1, "address.zipcode": 1, _id: 0 }
).limit(10);

// Name, street, and zipcode
db.restaurants.find(
  {},
  { 
    name: 1, 
    "address.street": 1, 
    "address.zipcode": 1,
    _id: 0 
  }
).limit(10);

// Most recent grade only
db.restaurants.find(
  {},
  { name: 1, "grades.0": 1, _id: 0 }
).limit(10);

// Movie essentials
db.movies.find(
  {},
  { title: 1, year: 1, "imdb.rating": 1, _id: 0 }
).limit(10);

// Action movies: title, year, rating, genres
db.movies.find(
  { genres: "Action" },
  { title: 1, year: 1, "imdb.rating": 1, genres: 1, _id: 0 }
).sort({ "imdb.rating": -1 })
.limit(10);

// Get first 2 grades only
db.restaurants.find(
  {},
  { name: 1, grades: { $slice: 2 }, _id: 0 }
).limit(5);

// Get last 2 grades
db.restaurants.find(
  {},
  { name: 1, grades: { $slice: -2 }, _id: 0 }
).limit(5);

// Get 2 grades, starting from 2nd (skip 1, take 2)
db.restaurants.find(
  {},
  { name: 1, grades: { $slice: [1, 2] }, _id: 0 }
).limit(5);

// Book catalog view
db.books.find(
  {},
  { 
    title: 1, 
    author_id: 1, 
    available_copies: 1,
    total_copies: 1,
    _id: 0 
  }
).limit(10);

// Available books only with minimal info
db.books.find(
  { available_copies: { $gt: 0 } },
  { title: 1, genre: 1, available_copies: 1, _id: 0 }
).limit(20);

// At least one grade element with BOTH conditions
db.restaurants.find({
  grades: {
    $elemMatch: {
      grade: "A",
      score: { $lt: 5 }
    }
  }
}).limit(10);

// Italian restaurants with perfect inspection
db.restaurants.find({
  cuisine: "Italian",
  grades: {
    $elemMatch: {
      grade: "A",
      score: { $lte: 3 }
    }
  }
}).limit(10);

// Example: Find movies with multiple genres including Action
db.movies.find({
  genres: { $all: ["Action", "Thriller"] }
}).limit(10);

// If cast were objects: { name: "...", role: "..." }
// db.movies.find({
//   cast: {
//     $elemMatch: {
//       name: "Tom Hanks",
//       role: "Lead"
//     }
//   }
// });

// Exactly 5 grades/inspections
db.restaurants.find({
  grades: { $size: 5 }
}).limit(10);

// Exactly 3 inspections
db.restaurants.find({
  grades: { $size: 3 }
}).limit(10);

// Count restaurants with exactly 5 grades
db.restaurants.countDocuments({ grades: { $size: 5 } });

// Movies with exactly 3 genres
db.movies.find({
  genres: { $size: 3 }
}).limit(10);

// Single genre movies
db.movies.find({
  genres: { $size: 1 }
}).limit(10);

// Must have BOTH Action AND Thriller
db.movies.find({
  genres: { $all: ["Action", "Thriller"] }
}).limit(10);

// Must have Action, Thriller, AND Sci-Fi
db.movies.find({
  genres: { $all: ["Action", "Thriller", "Sci-Fi"] }
}).limit(10);

// High-rated action thrillers
db.movies.find({
  genres: { $all: ["Action", "Thriller"] },
  "imdb.rating": { $gte: 7.0 }
}).sort({ "imdb.rating": -1 })
.limit(10);

// Books with both Fiction and Classic genres
db.books.find({
  genre: { $all: ["Fiction", "Classic"] }
}).limit(10);

// Available books with multiple genres
db.books.find({
  genre: { $all: ["Fiction", "Classic"] },
  available_copies: { $gt: 0 }
}).limit(10);

// Update one restaurant's name
db.restaurants.updateOne(
  { restaurant_id: "30075445" },
  { $set: { name: "Morris Park Bakery & Cafe" } }
);

// Check the result
db.restaurants.findOne({ restaurant_id: "30075445" });

// Update nested field (imdb.rating)
db.movies.updateOne(
  { title: "The Shawshank Redemption" },
  { $set: { "imdb.rating": 9.3, "imdb.votes": 2500000 } }
);

// Verify the update
db.movies.findOne(
  { title: "The Shawshank Redemption" },
  { title: 1, imdb: 1, _id: 0 }
);

// Decrement available copies (we'll use $inc later, but showing $set)
db.books.updateOne(
  { isbn: "978-0-06-112008-4" },
  { $set: { available_copies: 2 } }
);

// Better: check current value first
var book = db.books.findOne({ isbn: "978-0-06-112008-4" });
db.books.updateOne(
  { isbn: "978-0-06-112008-4" },
  { $set: { available_copies: book.available_copies - 1 } }
);

// Add field to all Manhattan restaurants
db.restaurants.updateMany(
  { borough: "Manhattan" },
  { $set: { verified: true } }
);

// Verify - count updated documents
db.restaurants.countDocuments({ 
  borough: "Manhattan", 
  verified: true 
});

// Add decade field to 90s movies
db.movies.updateMany(
  { year: { $gte: 1990, $lte: 1999 } },
  { $set: { decade: "1990s" } }
);

// Add to 2000s movies
db.movies.updateMany(
  { year: { $gte: 2000, $lte: 2009 } },
  { $set: { decade: "2000s" } }
);

// Verify
db.movies.find({ decade: "1990s" }).count();

// Mark classics
db.books.updateMany(
  { published_year: { $lt: 1950 } },
  { $set: { category: "classic", special_handling: true } }
);

// Count classics
db.books.countDocuments({ category: "classic" });

// Add multiple fields
db.restaurants.updateOne(
  { restaurant_id: "30075445" },
  { 
    $set: { 
      phone: "718-822-5200",
      email: "info@morrispark.com",
      website: "www.morrispark.com",
      "contact.manager": "John Smith"
    } 
  }
);

// $set creates nested objects if they don't exist
db.restaurants.updateOne(
  { restaurant_id: "30075445" },
  { 
    $set: { 
      "hours.monday": "9AM-9PM",
      "hours.tuesday": "9AM-9PM"
    } 
  }
);

// Remove single field
db.restaurants.updateMany(
  { borough: "Manhattan" },
  { $unset: { verified: "" } }
);

// Remove multiple fields (value doesn't matter, use "" or 1)
db.restaurants.updateOne(
  { restaurant_id: "30075445" },
  { 
    $unset: { 
      phone: "",
      email: "",
      website: ""
    } 
  }
);

// Verify field is gone
db.restaurants.findOne(
  { restaurant_id: "30075445" },
  { verified: 1, phone: 1 }
);

// Update nested imdb object
db.movies.updateOne(
  { title: "The Godfather" },
  { 
    $set: { 
      "imdb.rating": 9.2,
      "imdb.votes": 1800000,
      "awards.oscars": 3,
      "awards.nominations": 11
    } 
  }
);

// Add condition rating
db.books.updateOne(
  { isbn: "978-0-06-112008-4" },
  { $set: { condition: "excellent", last_checked: new Date() } }
);

// Remove temporary field
db.books.updateMany(
  {},
  { $unset: { temp_field: "" } }
);

// Increment votes by 100
db.movies.updateOne(
  { title: "Inception" },
  { $inc: { "imdb.votes": 100 } }
);

// Decrement (use negative value)
db.movies.updateOne(
  { title: "Inception" },
  { $inc: { "imdb.votes": -50 } }
);

// Multiple increments at once
db.movies.updateOne(
  { title: "Inception" },
  { $inc: { "imdb.votes": 100, view_count: 1 } }
);

// Book borrowed - decrement available
db.books.updateOne(
  { isbn: "978-0-06-112008-4" },
  { $inc: { available_copies: -1, total_borrowed: 1 } }
);

// Book returned - increment available
db.books.updateOne(
  { isbn: "978-0-06-112008-4" },
  { $inc: { available_copies: 1 } }
);

// Verify the update
db.books.findOne(
  { isbn: "978-0-06-112008-4" },
  { title: 1, available_copies: 1, total_borrowed: 1, _id: 0 }
);

// Increase late fee by 10% (multiply by 1.1)
db.books.updateMany(
  { late_fee: { $exists: true } },
  { $mul: { late_fee: 1.1 } }
);

// Apply 20% discount (multiply by 0.8)
db.books.updateMany(
  { price: { $exists: true } },
  { $mul: { price: 0.8 } }
);

// Double a value
db.books.updateOne(
  { isbn: "978-0-06-112008-4" },
  { $mul: { popularity_score: 2 } }
);

// Add new grade to the end of array
db.restaurants.updateOne(
  { restaurant_id: "30075445" },
  { 
    $push: { 
      grades: {
        date: new Date("2024-12-15"),
        grade: "A",
        score: 3
      }
    } 
  }
);

// Add to beginning (use $position with $each)
db.restaurants.updateOne(
  { restaurant_id: "30075445" },
  { 
    $push: { 
      grades: {
        $each: [{
          date: new Date("2024-12-15"),
          grade: "A",
          score: 3
        }],
        $position: 0
      }
    } 
  }
);

// Add single genre
db.movies.updateOne(
  { title: "Inception" },
  { $push: { genres: "Mystery" } }
);

// Add multiple genres at once
db.movies.updateOne(
  { title: "Inception" },
  { $push: { genres: { $each: ["Mystery", "Adventure"] } } }
);

// Add to cast array
db.movies.updateOne(
  { title: "Inception" },
  { $push: { cast: "Ellen Page" } }
);

// Remove last element from grades (-1)
db.restaurants.updateOne(
  { restaurant_id: "30075445" },
  { $pop: { grades: 1 } }
);

// Remove first element from grades (-1)
db.restaurants.updateOne(
  { restaurant_id: "30075445" },
  { $pop: { grades: -1 } }
);

// Add genre only if not present
db.movies.updateOne(
  { title: "Inception" },
  { $addToSet: { genres: "Sci-Fi" } }
);

// Try to add again - won't create duplicate
db.movies.updateOne(
  { title: "Inception" },
  { $addToSet: { genres: "Sci-Fi" } }
);

// Add multiple unique values
db.movies.updateOne(
  { title: "Inception" },
  { $addToSet: { genres: { $each: ["Thriller", "Mystery"] } } }
);

// Remove specific value
db.movies.updateOne(
  { title: "Inception" },
  { $pull: { genres: "Mystery" } }
);

// Remove with condition
db.movies.updateOne(
  { title: "Inception" },
  { $pull: { cast: "Unknown Actor" } }
);

// Remove grades with score > 40
db.restaurants.updateMany(
  {},
  { $pull: { grades: { score: { $gt: 40 } } } }
);

// Remove grades with grade "Z" (pending)
db.restaurants.updateMany(
  {},
  { $pull: { grades: { grade: "Z" } } }
);

// Remove multiple specific values
db.movies.updateOne(
  { title: "Inception" },
  { $pullAll: { genres: ["Mystery", "Adventure"] } }
);

// pullAll removes exact matches only
db.movies.updateMany(
  {},
  { $pullAll: { tags: ["outdated", "deprecated", "old"] } }
);

// Upsert: update if found, insert if not
db.books.updateOne(
  { isbn: "978-1-234-56789-0" },
  { 
    $set: { 
      title: "New Book Title",
      author: "Jane Doe",
      available_copies: 5
    } 
  },
  { upsert: true }
);

// Check the result
db.books.findOne({ isbn: "978-1-234-56789-0" });

// Update rating always, set created_at only on insert
db.movies.updateOne(
  { title: "New Movie 2024" },
  { 
    $set: { "imdb.rating": 8.0 },
    $setOnInsert: { 
      created_at: new Date(),
      year: 2024,
      genres: ["Action"]
    }
  },
  { upsert: true }
);

// Delete by unique identifier
db.restaurants.deleteOne({ restaurant_id: "99999999" });

// Delete first matching document
db.restaurants.deleteOne({ 
  borough: "Manhattan",
  cuisine: "Temporary" 
});

// Delete all matching documents
db.books.deleteMany({ 
  total_copies: 0,
  available_copies: 0 
});

// Delete old temp records
db.books.deleteMany({ 
  temp: true,
  created_at: { $lt: new Date("2023-01-01") }
});

// Soft delete - mark as deleted
db.movies.updateOne(
  { title: "Old Movie" },
  { 
    $set: { 
      deleted: true,
      deleted_at: new Date(),
      deleted_by: "admin"
    } 
  }
);

// Query active (non-deleted) documents
db.movies.find({ deleted: { $ne: true } });

// Permanently delete after 30 days (cleanup job)
db.movies.deleteMany({
  deleted: true,
  deleted_at: { $lt: new Date(Date.now() - 30*24*60*60*1000) }
});

// Create ascending index on borough
db.restaurants.createIndex({ borough: 1 });

// Create descending index
db.restaurants.createIndex({ cuisine: -1 });

// Create unique index (prevent duplicates)
db.restaurants.createIndex({ restaurant_id: 1 }, { unique: true });

// List all indexes
db.restaurants.getIndexes();

// Test performance improvement
db.restaurants.find({ borough: "Manhattan" }).explain("executionStats");

// Index on nested field using dot notation
db.restaurants.createIndex({ "address.zipcode": 1 });

// Index on array element
db.restaurants.createIndex({ "grades.grade": 1 });

// Query benefits from index
db.restaurants.find({ "address.zipcode": "10462" });

// List all indexes with details
db.movies.getIndexes();

// Create named index
db.movies.createIndex(
  { year: 1 },
  { name: "idx_year" }
);

// Drop specific index by name
db.movies.dropIndex("idx_year");

// Drop index by specification
db.movies.dropIndex({ year: 1 });

// Drop ALL indexes (except _id)
db.movies.dropIndexes();

// Compound index on two fields
db.restaurants.createIndex({ borough: 1, cuisine: 1 });

// This query uses the compound index
db.restaurants.find({ 
  borough: "Manhattan", 
  cuisine: "Italian" 
});

// This also uses the index (prefix match)
db.restaurants.find({ borough: "Manhattan" });

// This does NOT use the index efficiently
db.restaurants.find({ cuisine: "Italian" });

// Common query: filter by genre, sort by rating, limit results
// BAD index order:
db.movies.createIndex({ "imdb.rating": -1, genres: 1 });

// GOOD index order (ESR rule):
db.movies.createIndex({ genres: 1, "imdb.rating": -1 });

// Query that benefits:
db.movies.find({ genres: "Action" })
  .sort({ "imdb.rating": -1 })
  .limit(10);

// Compound with 3 fields
db.movies.createIndex({ 
  genres: 1, 
  year: 1, 
  "imdb.rating": -1 
});

// Create index for aggregation
db.restaurants.createIndex({ 
  borough: 1, 
  cuisine: 1, 
  "grades.score": 1 
});

// Aggregation that uses the index
db.restaurants.aggregate([
  { $match: { borough: "Manhattan", cuisine: "Italian" } },
  { $sort: { "grades.score": 1 } },
  { $limit: 10 }
]);

// Create text index on single field
db.movies.createIndex({ title: "text" });

// Text index on multiple fields
db.movies.createIndex({ 
  title: "text", 
  plot: "text",
  genres: "text"
});

// Text search query
db.movies.find({ $text: { $search: "redemption prison" } });

// Search with exact phrase
db.movies.find({ $text: { $search: "\"the godfather\"" } });

// Exclude words with minus
db.movies.find({ $text: { $search: "action -comedy" } });

// Search with text score (relevance)
db.movies.find(
  { $text: { $search: "love story romance" } },
  { score: { $meta: "textScore" } }
).sort({ score: { $meta: "textScore" } })
.limit(10);

// Filter by score threshold
db.movies.find(
  { 
    $text: { $search: "action adventure" },
    score: { $meta: "textScore" }
  },
  { score: { $meta: "textScore" } }
).sort({ score: { $meta: "textScore" } });

// Title is 3x more important than description
db.books.createIndex(
  { 
    title: "text", 
    description: "text" 
  },
  { 
    weights: {
      title: 3,
      description: 1
    }
  }
);

// Search benefits from weighting
db.books.find(
  { $text: { $search: "mockingbird" } },
  { score: { $meta: "textScore" } }
).sort({ score: { $meta: "textScore" } });

// Create 2dsphere index on coordinates
db.restaurants.createIndex({ "address.coord": "2dsphere" });

// Check data format (must be [longitude, latitude])
db.restaurants.findOne({}, { "address.coord": 1 });
// Should show: "coord": [-73.856077, 40.848447]

// Find restaurants near Times Square
db.restaurants.find({
  "address.coord": {
    $near: {
      $geometry: {
        type: "Point",
        coordinates: [-73.9851, 40.7589]
      },
      $maxDistance: 1000  // in meters
    }
  }
}).limit(10);

// Find with min and max distance
db.restaurants.find({
  "address.coord": {
    $near: {
      $geometry: {
        type: "Point",
        coordinates: [-73.9851, 40.7589]
      },
      $minDistance: 100,
      $maxDistance: 500
    }
  }
});

// Define a polygon area (Manhattan neighborhood)
db.restaurants.find({
  "address.coord": {
    $geoWithin: {
      $geometry: {
        type: "Polygon",
        coordinates: [[
          [-73.99, 40.75],   // Point 1
          [-73.98, 40.75],   // Point 2
          [-73.98, 40.76],   // Point 3
          [-73.99, 40.76],   // Point 4
          [-73.99, 40.75]    // Back to Point 1 (close polygon)
        ]]
      }
    }
  }
});

// Find within circle (center + radius)
db.restaurants.find({
  "address.coord": {
    $geoWithin: {
      $centerSphere: [
        [-73.9851, 40.7589],  // center [lng, lat]
        1 / 6378.1            // radius in radians (≈1km)
      ]
    }
  }
});

// Use $geoNear in aggregation (must be first stage)
db.restaurants.aggregate([
  {
    $geoNear: {
      near: {
        type: "Point",
        coordinates: [-73.9851, 40.7589]
      },
      distanceField: "distance",
      maxDistance: 5000,
      spherical: true
    }
  },
  {
    $group: {
      _id: {
        $switch: {
          branches: [
            { case: { $lte: ["$distance", 500] }, then: "0-500m" },
            { case: { $lte: ["$distance", 1000] }, then: "500m-1km" },
            { case: { $lte: ["$distance", 2000] }, then: "1-2km" }
          ],
          default: "2-5km"
        }
      },
      count: { $sum: 1 },
      avgScore: { $avg: "$grades.0.score" }
    }
  },
  { $sort: { _id: 1 } }
]);

// Test WITHOUT index
db.restaurants.dropIndex({ borough: 1 });

var start = new Date();
db.restaurants.find({ borough: "Manhattan" }).count();
var end = new Date();
print("Without index: " + (end - start) + "ms");

// Test WITH index
db.restaurants.createIndex({ borough: 1 });

start = new Date();
db.restaurants.find({ borough: "Manhattan" }).count();
end = new Date();
print("With index: " + (end - start) + "ms");

// Typical results:
// Without index: 45ms (collection scan)
// With index: 2ms (index scan) - 22x faster!

// Create compound index
db.movies.createIndex({ year: 1, title: 1 });

// Covered query (all fields from index)
db.movies.find(
  { year: 2010 },
  { _id: 0, year: 1, title: 1 }
);

// Verify it's covered
db.movies.find(
  { year: 2010 },
  { _id: 0, year: 1, title: 1 }
).explain("executionStats");
// Look for: totalDocsExamined: 0 (covered!)

// Check cardinality (unique values)
db.restaurants.distinct("borough").length;
// Result: 5 (low cardinality - not ideal alone)

db.restaurants.distinct("cuisine").length;
// Result: 84 (medium cardinality - good)

db.restaurants.distinct("restaurant_id").length;
// Result: 3772 (high cardinality - excellent)

// Good compound index (high selectivity together)
db.restaurants.createIndex({ borough: 1, cuisine: 1 });
// 5 boroughs × 84 cuisines = 420 combinations

// Basic explain
db.restaurants.find({ borough: "Manhattan" }).explain();

// Execution stats (detailed)
db.restaurants.find({ borough: "Manhattan" })
  .explain("executionStats");

// All plans (shows rejected plans too)
db.restaurants.find({ borough: "Manhattan" })
  .explain("allPlansExecution");

// Compare two approaches
// Query 1: Without index
db.movies.find({ year: 2010 }).explain("executionStats");

// Query 2: With index
db.movies.createIndex({ year: 1 });
db.movies.find({ year: 2010 }).explain("executionStats");

// Find slow queries in logs
db.setProfilingLevel(1, { slowms: 100 });

// Check which indexes are used
db.restaurants.aggregate([
  { $indexStats: {} }
]);

// Find unused indexes (accesses.ops = 0)

// $match works like find()
db.restaurants.aggregate([
  { $match: { borough: "Manhattan" } }
]);

// Multiple conditions
db.restaurants.aggregate([
  { 
    $match: { 
      borough: "Manhattan",
      cuisine: "Italian"
    } 
  }
]);

// With comparison operators
db.restaurants.aggregate([
  { 
    $match: { 
      borough: "Manhattan",
      "grades.0.score": { $lt: 10 }
    } 
  }
]);

// Filter by decade and genre
db.movies.aggregate([
  { 
    $match: { 
      year: { $gte: 2010, $lte: 2019 },
      genres: "Action",
      "imdb.rating": { $gte: 7.0 }
    } 
  }
]);

// Group by borough and count
db.restaurants.aggregate([
  {
    $group: {
      _id: "$borough",
      count: { $sum: 1 }
    }
  }
]);

// Sort by count descending
db.restaurants.aggregate([
  {
    $group: {
      _id: "$borough",
      count: { $sum: 1 }
    }
  },
  { $sort: { count: -1 } }
]);

// Average score by cuisine
db.restaurants.aggregate([
  {
    $group: {
      _id: "$cuisine",
      avgScore: { $avg: "$grades.0.score" },
      count: { $sum: 1 }
    }
  },
  { $sort: { avgScore: 1 } },
  { $limit: 10 }
]);

// Group by decade (calculated field)
db.movies.aggregate([
  {
    $group: {
      _id: { 
        $subtract: [ 
          "$year", 
          { $mod: ["$year", 10] } 
        ] 
      },
      avgRating: { $avg: "$imdb.rating" },
      movieCount: { $sum: 1 },
      maxRating: { $max: "$imdb.rating" },
      minRating: { $min: "$imdb.rating" }
    }
  },
  { $sort: { _id: -1 } }
]);

// Complex aggregation with multiple accumulators
db.restaurants.aggregate([
  { $match: { borough: "Manhattan" } },
  {
    $group: {
      _id: "$cuisine",
      totalRestaurants: { $sum: 1 },
      avgScore: { $avg: "$grades.0.score" },
      bestScore: { $min: "$grades.0.score" },
      worstScore: { $max: "$grades.0.score" },
      restaurants: { $push: "$name" }
    }
  },
  { $sort: { totalRestaurants: -1 } },
  { $limit: 5 }
]);

// Genre statistics
db.books.aggregate([
  {
    $group: {
      _id: "$genre",
      totalBooks: { $sum: 1 },
      totalCopies: { $sum: "$total_copies" },
      availableCopies: { $sum: "$available_copies" },
      avgPublicationYear: { $avg: "$published_year" }
    }
  },
  { $sort: { totalBooks: -1 } }
]);

// Select and rename fields
db.restaurants.aggregate([
  { $match: { borough: "Manhattan" } },
  {
    $project: {
      _id: 0,
      restaurantName: "$name",
      location: "$borough",
      foodType: "$cuisine",
      latestGrade: "$grades.0.grade",
      latestScore: "$grades.0.score"
    }
  },
  { $limit: 10 }
]);

// Calculate age of movie
db.movies.aggregate([
  {
    $project: {
      title: 1,
      year: 1,
      rating: "$imdb.rating",
      ageInYears: { $subtract: [2024, "$year"] },
      isClassic: { $gte: [{ $subtract: [2024, "$year"] }, 30] },
      ratingCategory: {
        $switch: {
          branches: [
            { case: { $gte: ["$imdb.rating", 8.0] }, then: "Excellent" },
            { case: { $gte: ["$imdb.rating", 7.0] }, then: "Good" },
            { case: { $gte: ["$imdb.rating", 5.0] }, then: "Average" }
          ],
          default: "Poor"
        }
      }
    }
  },
  { $limit: 10 }
]);

// String manipulation
db.books.aggregate([
  {
    $project: {
      title: 1,
      titleUpperCase: { $toUpper: "$title" },
      titleLowerCase: { $toLower: "$title" },
      titleLength: { $strLenCP: "$title" },
      yearString: { $toString: "$published_year" },
      availability: {
        $concat: [
          { $toString: "$available_copies" },
          " of ",
          { $toString: "$total_copies" },
          " available"
        ]
      }
    }
  },
  { $limit: 5 }
]);

// Complete pipeline: match → group → sort → limit
db.restaurants.aggregate([
  { $match: { borough: "Manhattan" } },
  {
    $group: {
      _id: "$cuisine",
      count: { $sum: 1 },
      avgScore: { $avg: "$grades.0.score" }
    }
  },
  { $sort: { count: -1 } },
  { $limit: 10 },
  {
    $project: {
      _id: 0,
      cuisine: "$_id",
      restaurantCount: "$count",
      averageHealthScore: { $round: ["$avgScore", 1] }
    }
  }
]);

// Page 1 (skip 0, limit 10)
var page = 1;
var pageSize = 10;

db.movies.aggregate([
  { $match: { "imdb.rating": { $gte: 7.0 } } },
  { $sort: { "imdb.rating": -1 } },
  { $skip: (page - 1) * pageSize },
  { $limit: pageSize },
  {
    $project: {
      _id: 0,
      title: 1,
      year: 1,
      rating: "$imdb.rating"
    }
  }
]);

// Unwind creates separate doc for each array element
db.restaurants.aggregate([
  { $match: { restaurant_id: "30075445" } },
  { $unwind: "$grades" },
  {
    $project: {
      name: 1,
      gradeDate: "$grades.date",
      grade: "$grades.grade",
      score: "$grades.score"
    }
  }
]);

// Unwind genres, then group
db.movies.aggregate([
  { $unwind: "$genres" },
  {
    $group: {
      _id: "$genres",
      movieCount: { $sum: 1 },
      avgRating: { $avg: "$imdb.rating" }
    }
  },
  { $sort: { movieCount: -1 } },
  { $limit: 10 }
]);

// JOIN books with authors (like SQL LEFT JOIN)
db.books.aggregate([
  {
    $lookup: {
      from: "authors",
      localField: "author_id",
      foreignField: "_id",
      as: "authorDetails"
    }
  },
  {
    $project: {
      title: 1,
      isbn: 1,
      authorName: { $arrayElemAt: ["$authorDetails.name", 0] },
      authorBio: { $arrayElemAt: ["$authorDetails.bio", 0] }
    }
  },
  { $limit: 10 }
]);

// Books with loan history
db.books.aggregate([
  {
    $lookup: {
      from: "loans",
      localField: "_id",
      foreignField: "book_id",
      as: "loanHistory"
    }
  },
  {
    $project: {
      title: 1,
      totalLoans: { $size: "$loanHistory" },
      currentlyBorrowed: {
        $size: {
          $filter: {
            input: "$loanHistory",
            cond: { $eq: ["$$this.returned", false] }
          }
        }
      }
    }
  },
  { $sort: { totalLoans: -1 } },
  { $limit: 10 }
]);

// Triple join: borrowers → loans → books
db.borrowers.aggregate([
  {
    $lookup: {
      from: "loans",
      localField: "_id",
      foreignField: "borrower_id",
      as: "loans"
    }
  },
  { $unwind: "$loans" },
  {
    $lookup: {
      from: "books",
      localField: "loans.book_id",
      foreignField: "_id",
      as: "bookDetails"
    }
  },
  {
    $project: {
      borrowerName: "$name",
      bookTitle: { $arrayElemAt: ["$bookDetails.title", 0] },
      borrowDate: "$loans.borrow_date",
      dueDate: "$loans.due_date",
      isOverdue: {
        $gt: [new Date(), "$loans.due_date"]
      }
    }
  },
  { $match: { isOverdue: true } }
]);

// Find best cuisines by average score and count
db.restaurants.aggregate([
  // Stage 1: Filter Manhattan only
  { $match: { borough: "Manhattan" } },
  
  // Stage 2: Unwind grades array
  { $unwind: "$grades" },
  
  // Stage 3: Group by cuisine
  {
    $group: {
      _id: "$cuisine",
      avgScore: { $avg: "$grades.score" },
      restaurantCount: { $sum: 1 },
      totalInspections: { $sum: 1 }
    }
  },
  
  // Stage 4: Filter cuisines with 50+ restaurants
  { $match: { restaurantCount: { $gte: 50 } } },
  
  // Stage 5: Sort by average score
  { $sort: { avgScore: 1 } },
  
  // Stage 6: Limit to top 10
  { $limit: 10 },
  
  // Stage 7: Format output
  {
    $project: {
      _id: 0,
      cuisine: "$_id",
      averageScore: { $round: ["$avgScore", 2] },
      restaurants: "$restaurantCount",
      inspections: "$totalInspections"
    }
  }
]);

// Decade-by-decade movie analysis
db.movies.aggregate([
  {
    $group: {
      _id: { 
        $multiply: [
          { $floor: { $divide: ["$year", 10] } },
          10
        ]
      },
      totalMovies: { $sum: 1 },
      avgRating: { $avg: "$imdb.rating" },
      topRatedMovie: { 
        $max: { 
          rating: "$imdb.rating", 
          title: "$title" 
        } 
      }
    }
  },
  { $sort: { _id: 1 } },
  {
    $project: {
      decade: { $concat: [{ $toString: "$_id" }, "s"] },
      totalMovies: 1,
      avgRating: { $round: ["$avgRating", 2] },
      _id: 0
    }
  }
]);

// Comprehensive health report by borough and cuisine
db.restaurants.aggregate([
  { $unwind: "$grades" },
  {
    $group: {
      _id: {
        borough: "$borough",
        cuisine: "$cuisine"
      },
      avgScore: { $avg: "$grades.score" },
      restaurantCount: { $addToSet: "$restaurant_id" },
      gradeDistribution: {
        $push: "$grades.grade"
      }
    }
  },
  {
    $project: {
      borough: "$_id.borough",
      cuisine: "$_id.cuisine",
      avgScore: { $round: ["$avgScore", 2] },
      totalRestaurants: { $size: "$restaurantCount" },
      gradeACoun: {
        $size: {
          $filter: {
            input: "$gradeDistribution",
            cond: { $eq: ["$$this", "A"] }
          }
        }
      },
      _id: 0
    }
  },
  { $sort: { borough: 1, avgScore: 1 } }
]);

// Most popular books with availability status
db.books.aggregate([
  {
    $lookup: {
      from: "loans",
      localField: "_id",
      foreignField: "book_id",
      as: "loanHistory"
    }
  },
  {
    $project: {
      title: 1,
      genre: 1,
      totalLoans: { $size: "$loanHistory" },
      available_copies: 1,
      total_copies: 1,
      utilizationRate: {
        $multiply: [
          {
            $divide: [
              { $subtract: ["$total_copies", "$available_copies"] },
              "$total_copies"
            ]
          },
          100
        ]
      }
    }
  },
  { $sort: { totalLoans: -1 } },
  { $limit: 20 }
]);

// Recommendation based on genre overlap and rating
var userFavoriteGenres = ["Action", "Sci-Fi"];

db.movies.aggregate([
  { $unwind: "$genres" },
  { $match: { genres: { $in: userFavoriteGenres } } },
  {
    $group: {
      _id: "$_id",
      title: { $first: "$title" },
      year: { $first: "$year" },
      rating: { $first: "$imdb.rating" },
      allGenres: { $push: "$genres" },
      genreMatchCount: { $sum: 1 }
    }
  },
  { $match: { rating: { $gte: 7.0 } } },
  { $sort: { genreMatchCount: -1, rating: -1 } },
  { $limit: 10 },
  {
    $project: {
      _id: 0,
      title: 1,
      year: 1,
      rating: 1,
      matchedGenres: "$genreMatchCount"
    }
  }
]);