1 a. Illustration of Where Clause, AND, OR operations in MongoDB.


➢ Creating database
>use ProgrammingBooks

➢ create the collection inside the database
>db.createCollection("BookDetails")

➢ Inserting Documents into collection
Method 1: Insert the single value or document
>db.BookDetails.insertOne({
_id: 1,
title: "Clean Code",
author: "Robert C. Martin",
category: "Software Development",

year: 2008
})


Method 2: Insert multiples values or document in collection
>db.BookDetails.insertMany([
{_id: 2, title: "Clean Code", author: "Robert C. Martin", category: "Software
Development", year: 2008 },
{_id: 3, title: "JavaScript: The Good Parts", author: "Douglas Crockford",
category: "JavaScript", year: 2008 },
{_id: 4, title: "Design Patterns", author: "Erich Gamma", category: "Software
Design", year: 1994 },
{_id: 5, title: "Introduction to Algorithms", author: "Thomas H. Cormen",
category: "Algorithms", year: 2009 },
{_id: 6, title: "Python Crash Course", author: "Eric Matthes", category:
"Python", year: 2015 }
] );


➢ Displaying documents within the collection
> db.BookDetails.find( )
Or
> db.BookDetails.find( ).pretty( )

➢ Displaying documents based on condition/s(using $where)
>db.BookDetails.find({ year: 2008 }).pretty( )

Or
> db.BookDetails.find({ year:{$eq: 2008 }}).pretty( )
Or

> db.BookDetails.find({$where:"this.year==2008"}).pretty()

➢ Combining multiple conditions using $and and $or

db.BookDetails.find({
$and: [
{ category: "Software Development" },
{ year: 2008 }
]
}).pretty()

db.BookDetails.find({
$or: [
{ category: "JavaScript" },
{ year: 2015 }
]
}).pretty()


db.BookDetails.find({category:{$in:['javascript','Software Development']}})
.pretty( )



1 b. Execute the Commands of MongoDB and operations in MongoDB:
Insert, Query, Update, Delete and Projection. (Note: use any collection)

>db.BookDetails.find({ year: { $gt: 2000 } }).pretty( )
>db.BookDetails.find({ year: { $gte: 2000 } }).pretty( )
>db.BookDetails.find({ year: { $lt: 2000 } }).pretty( )
>db.BookDetails.find({ year: { $lte: 2000 } }).pretty( )
>db.BookDetails.find({ year: { $ne: 2000 } }).pretty( )
>db.BookDetails.find({category:{$in:['javascript','Software Development']}}).pretty( )
>db.BookDetails.find({category:{$nin:['javascript','Software Development']}}).pretty( )



>db.BookDetails.updateOne(
{ title: "Clean Code: A Handbook of Agile Software Craftsmanship" },
{ $set: { author: "vtucode" } }
)

Method 2: updating mutiple documents(using updateMany( ))
>db.BookDetails.updateMany(
{ year: { $lt: 2010 } },
{ $set: { category: "vtucode website" } }
)


Delete the documents
Method 1: Delete single value or document in collection
>db.BookDetails.deleteOne({ _id: 2 })

Method 2: Delete multiple values or document in collection
>db.BookDetails.deleteMany({ year: { $lt: 1995 } })


Method 3: Delete all documents
>db.BookDetails.deleteMany({ })




2 a. Develop a MongoDB query to select certain fields and ignore some
fields of the documents from any collection.

➢ Including Specific Fields:

>db.BookDetails.find({ },{category:1,title:1,price:1}) //with _id filed by default
>db.BookDetails.find({},{category:1,title:1,price:1,_id:0}) //exclude _id field

➢ Excluding Specific Fields:
>db.BookDetails.find({title:'Clean Code'},{category:0,_id:0}) //excluding multiple fields


2 b. Develop a MongoDB query to display the first 5 documents from the results
obtained.[use of limit and find]

>db.BookDetails.countDocuments( ) //displays number of documents in the collection
>db.BookDetails.find().limit(5) //displays first 5 documents
>db.BookDetails.find({},{category:1,title:1,price:1,_id:0}).limit(5)
>db.BookDetails.find().skip(2) //displays 3rd document onwards by skipping first 2




3 a Execute query selectors (comparison selectors, logical selectors ) and list
out the results on any collection

➢ create the new database as well as switch the database
>use companyDB

➢ insert the multiple values or documents

>db.Employees.insertMany([
{ name: "Alice", age: 30, department: "HR", salary: 50000,
joinDate: new Date("2015-01-15") },
{ name: "Bob", age: 24, department: "Engineering", salary: 70000,
joinDate: new Date("2019-03-10") },
{ name: "Charlie", age: 29, department: "Engineering", salary: 75000,
joinDate: new Date("2017-06-23") },
{ name: "David", age: 35, department: "Marketing", salary: 60000,
joinDate: new Date("2014-11-01") },
{ name: "Eve", age: 28, department: "Finance", salary: 80000,
joinDate: new Date("2018-08-19") }
])


➢ $eq (Equal)
>db.Employees.find({ department: { $eq: "Engineering" } }).pretty( )
➢ $ne (Not Equal)
>db.Employees.find({ department: { $ne: "HR" } }).pretty( )

➢ $gt (Greater than)
>db.Employees.find({ age: { $gt: 30 } }).pretty()
➢ $lt (less than)
>db.Employees.find({ salary: { $lt: 70000 } }).pretty()
➢ $gte (Greater Than or Equal)
>db.Employees.find({ joinDate: { $gte: new Date("2018-01-01") } }).pretty()
➢ $lte (Less Than or Equal)
>db.Employees.find({ age: { $lte: 28 } }).pretty()


$and (Logical AND)
Example: Find employees who are in the “Engineering” department and have a salary greater
than 70000.

>db.Employees.find({ $and: [ { department: "Engineering" }, { salary: { $gt: 70000 } } ]
}).pretty( )
➢ $or (Logical OR)

Example: Find employees who are either in the “HR” department or have a salary less than
60000.
>db.Employees.find({ $or: [ { department: "HR" }, { salary: { $lt: 60000 } } ] }).pretty()


$not (Logical NOT)

>db.Employees.find({ department: { $not: { $eq: "Engineering" } } }).pretty( )
➢ $nor (Logical NOR)

Example: Find employees who are neither in the “HR” department nor have a salary greater
than 75000.
>db.Employees.find({ $nor: [ { department: "HR" }, { salary: { $gt: 75000 } } ] }).pretty()


3 b) Execute query selectors (Geospatial selectors, Bitwise selectors ) and list
out the results on any collection

Create a geospatial index
db.Places.createIndex({ location: "2dsphere" })


$near (Find places near a certain point):

Example: Find places near a specific coordinate, for example, near Times Square.
db.Places.find({
location: {
$near: {
$geometry: {
type: "Point",
coordinates: [-73.9851, 40.7580]
},
$maxDistance: 5000 // distance in meters
}}}).pretty( )

$geoWithin (Find places within a specific area):

Example: Find places within a specific polygon, for example, an area covering part of
Manhattan.

db.Places.find({
location: {
$geoWithin: {
$geometry: {
type: "Polygon",
coordinates: [
[
[-70.016, 35.715],
[-74.014, 40.717],
[-73.990, 40.730],
[-73.990, 40.715],
[-70.016, 35.715]
]]}}}}).pretty()


Bitwise selectors:

➢Insert documents into the collection

>db.employees.insertMany([
{ name: "Alice", flags: 1 },
{ name: "Bob", flags: 3 }, // Active + Manager
{ name: "Charlie", flags: 5 }, // Active + Remote Access
{ name: "David", flags: 10 }, // Manager + Probation
{ name: "Eve", flags: 15 } // All flags set
])

1. Get all active employees (bit 0 set)
db.employees.find({ flags: { $bitsAllSet: 1 } })

2. Get employees who are either managers OR have remote access (bit 1 or 2 set)

db.employees.find({ flags: { $bitsAnySet: [1, 2] } })

3. Get employees who are not on probation (bit 3 is clear)

db.employees.find({ flags: { $bitsAllClear: 8 } })

4. Get employees who are active and managers (bit 0 and 1 set)

db.employees.find({ flags: { $bitsAllSet: [0, 1] } })

5. Get employees where any flag is clear (not all permissions)

db.employees.find({ flags: { $bitsAnyClear: [0, 1, 2, 3] } })



4 Create and demonstrate how projection operators ($, $elematch and $slice)
would be used in the MongoDB. 

➢Create database
>use retailDB
➢Create collection
>db.creteCollection(‘Products’)
➢Insert documents into the collection

db.Products.insertMany([
{
name: "Laptop",
brand: "BrandA",
features: [
{ name: "Processor", value: "Intel i7" },
{ name: "RAM", value: "16GB" },
{ name: "Storage", value: "512GB SSD" }
],
reviews: [
{ user: "Alice", rating: 5, comment: "Excellent!" },
{ user: "Bob", rating: 4, comment: "Very good" },
{ user: "Charlie", rating: 3, comment: "Average" }
]
},

{
name: "Smartphone",
brand: "BrandB",
features: [
{ name: "Processor", value: "Snapdragon 888" },
{ name: "RAM", value: "8GB" },
{ name: "Storage", value: "256GB" }
],
reviews: [
{ user: "Dave", rating: 4, comment: "Good phone" },
{ user: "Eve", rating: 2, comment: "Not satisfied" }
]
}
])


Projection Operators:

db.Products.find(
{ name: "Laptop", "reviews.user": "Alice" },
{ "reviews.$": 1 }
).pretty( )

or

db.Products.find(
{ name: "Laptop" },
{ reviews: { $elemMatch: { rating: { $gt: 4 } } } }
).pretty( )

or

>db.Products.find(
{ name: "Smartphone" },
{ reviews: { $slice: 1 } }
).pretty( )



5. Execute Aggregation operations ($avg, $min,$max, $push, $addToSet etc.). students
encourage to execute several queries to demonstrate various aggregation operators)
➢ Create database
> use salesDB

➢ Create collection
>db.createCollection(‘Sales’)

> db.Sales.insertMany([
{ date: new Date("2024-01-01"), product: "Laptop", price: 1200, quantity: 1, customer: "Amar" },
{ date: new Date("2024-01-02"), product: "Laptop", price: 1200, quantity: 2, customer: "Babu" },

{ date: new Date("2024-01-03"), product: "Mouse", price: 25, quantity: 5, customer: "Chandra" },
{ date: new Date("2024-01-04"), product: "Keyboard", price: 45, quantity: 3, customer: "Amar" },
{ date: new Date("2024-01-05"), product: "Monitor", price: 300, quantity: 1, customer: "Babu" },
{ date: new Date("2024-01-06"), product: "Laptop", price: 1200, quantity: 1, customer: "Deva" }
])


Aggregation Operations:

1. $avg (Average): Calculate the average price of each product.
> db.Sales.aggregate([ { $group: { _id: "$product",
averagePrice: { $avg: "$price" } } } ]).pretty( )

2. $min (Minimum): Find the minimum price of each product.
> db.Sales.aggregate([ { $group: { _id: "$product",
minPrice: { $min: "$price" } } } ]).pretty()

3. $max (Maximum): Find the maximum price of each product.
> db.Sales.aggregate([ { $group: { _id: "$product",
maxPrice: { $max: "$price" } } } ]).pretty()

4. $push (Push Values to an Array): Group sales by customer and push each purchased product
into an array.
> db.Sales.aggregate([ { $group: { _id: "$customer",
products: { $push: "$product" } } } ]).pretty()

5. $addToSet (Add Unique Values to an Array): Group sales by customer and add each unique
purchased product to an array.

> db.Sales.aggregate([ { $group: { _id: "$customer",
uniqueProducts: { $addToSet: "$product" } } } ]).pretty()





6. Execute Aggregation Pipeline and its operations (pipeline must contain $match, $group,
$sort, $project,$skip etc. students encourage to execute several queries to demonstrate
various aggregation operators)


➢ Create database
>use restaurantDB

➢ Create collection
>db.createCollection(‘restaurants’)

➢ Insert documents
db.restaurants.insertMany([
{
name: "Biryani House",
cuisine: "Indian",
location: "Jayanagar",
reviews: [
{ user: "Aarav", rating: 5, comment: "Amazing biryani!" },
{ user: "Bhavana", rating: 4, comment: "Great place!" }
]
},
{
name: "Burger Joint",
cuisine: "American",
location: "Koramangala",
reviews: [
{ user: "Chirag", rating: 3, comment: "Average burger" },
{ user: "Devika", rating: 4, comment: "Good value" }
]
},
{
name: "Pasta House",
cuisine: "Italian",
location: "Rajajinagar",
reviews: [
{ user: "Esha", rating: 5, comment: "Delicious pasta!" },
{ user: "Farhan", rating: 4, comment: "Nice ambiance" }
]
},

{
name: "Curry Palace",
cuisine: "Indian",
location: "Jayanagar",
reviews: [
{ user: "Gaurav", rating: 4, comment: "Spicy and tasty!" },
{ user: "Harini", rating: 5, comment: "Best curry in town!" }
]
},
{
name: "Taco Stand",
cuisine: "Mexican",
location: "Jayanagar",
reviews: [
{ user: "Ishaan", rating: 5, comment: "Fantastic tacos!" },
{ user: "Jaya", rating: 4, comment: "Very authentic" }
]
}
])

Aggregation Pipeline and its operations:

db.restaurants.aggregate([
{
$match: {
"location": "Jayanagar"
}
},
{
$unwind: "$reviews"
},
{
$group: {
_id: "$name",
averageRating: { $avg: "$reviews.rating" },
totalReviews: { $sum: 1 }
}
},
{
$sort: {
averageRating: -1
}
},
{
$project: {
_id: 0,
restaurant: "$_id",
averageRating: 1,
totalReviews: 1
}
},
{
$skip: 1
}
]).pretty( )





7 a. Find all listings with listing_url, name, address, host_picture_url in the listings And
Reviews collection that have a host with a picture url

➢ Create database
> use vacationRentals
➢ Create collection
>db.createCollection(‘listingsAndReviews’)
➢ Insert documents
db.listingsAndReviews.insertMany([
{
listing_url: "http://www.example.com/listing/123456",
name: "Beautiful Apartment",
address: {
street: "123 Main Street",
suburb: "Central",
city: "Metropolis",
country: "Wonderland"
},
host: {
name: "Alice",
picture_url: "http://www.example.com/images/host/host123.jpg"
}
},
{
listing_url: "http://www.example.com/listing/654321",
name: "Cozy Cottage",
address: {
street: "456 Another St",
suburb: "North",
city: "Smallville",
country: "Wonderland"
},
host: {
name: "Bob",
picture_url: ""
}
},

{
listing_url: "http://www.example.com/listing/789012",
name: "Modern Condo",
address: {
street: "789 Side Road",
suburb: "East",
city: "Gotham",
country: "Wonderland"
},
host: {
name: "Charlie",
picture_url: "http://www.example.com/images/host/host789.jpg"
}
}
])


➢ Query to Find Listings with Host Picture URLs: (use $exists)

db.listingsAndReviews.find(
{
"host.picture_url": { $exists: true, $ne: "" }
},
{
listing_url: 1,
name: 1,
address: 1,
"host.picture_url": 1
}
).pretty()




7 b. Using E-commerce collection write a query to display reviews summary.
➢ Create database
> use ecommerce
➢ Create collection
>db.createCollection(‘products’)

➢ Insert documents
db.products.insertMany([
{
product_id: 1,
name: "Laptop",
category: "Electronics",
price: 1200,
reviews: [
{ user: "Alice", rating: 5, comment: "Excellent!" },
{ user: "Bob", rating: 4, comment: "Very good" },
{ user: "Charlie", rating: 3, comment: "Average" }
]
},
{
product_id: 2,
name: "Smartphone",
category: "Electronics",
price: 800,
reviews: [
{ user: "Dave", rating: 4, comment: "Good phone" },
{ user: "Eve", rating: 2, comment: "Not satisfied" },
{ user: "Frank", rating: 5, comment: "Amazing!" }
]
},
{
product_id: 3,
name: "Headphones",
category: "Accessories",
price: 150,
reviews: [
{ user: "Grace", rating: 5, comment: "Great sound" },
{ user: "Heidi", rating: 3, comment: "Okay" }
]
}
])


➢ Query to Find E-commerce collection:

>db.products.aggregate([
{
$unwind: "$reviews"
},
{
$group: {
_id: "$name",
totalReviews: { $sum: 1 },
averageRating: { $avg: "$reviews.rating" },
comments: { $push: "$reviews.comment" }
}
},
{
$project: {
_id: 0,
product: "$_id",
totalReviews: 1,
averageRating: 1,
comments: 1
}
}
]).pretty( )



8 a. Demonstrate creation of different types of indexes on collection
(unique, sparse, compound and multikey indexes)

➢ Insert Sample Documents
db.employees.insertMany([
{ name: "Alice", email: "alice@example.com", dept: "HR", skills: ["communication", "leadership"]
},
{ name: "Bob", email: "bob@example.com", dept: "Engineering", skills: ["JavaScript", "MongoDB"]
},
{ name: "Charlie", dept: "HR", skills: ["recruitment"] },
{ name: "David", email: "david@example.com", dept: "Engineering" },
{ name: "Eve", email: "eve@example.com", dept: "Finance", skills: ["accounting", "Excel"] }])


1. Unique Index on Email
>db.employees.createIndex({ email: 1 }, { unique: true })

2. Sparse Index on Email
>db.employees.createIndex({ email: 1 }, { sparse: true })

3. Compound Index on Department and Name
>db.employees.createIndex({ dept: 1, name: 1 })

4. Multikey Index on Skills (Array Field)
>db.employees.createIndex({ skills: 1 })


5. View All Indexes on the Collection
>db.employees.getIndexes( )



8 b. Demonstrate optimization of queries using indexes.
➢ Insert Sample Documents
>db.products.insertMany([
{ name: "Laptop", category: "Electronics", price: 1000, stock: 10 },
{ name: "Smartphone", category: "Electronics", price: 500, stock: 50 },
{ name: "Desk Chair", category: "Furniture", price: 150, stock: 5 },
{ name: "Notebook", category: "Stationery", price: 5, stock: 100 },
{ name: "Monitor", category: "Electronics", price: 200, stock: 20 }
]) 
➢ Run a Query Without an Index
>db.products.find({ category: "Electronics", price: { $lt: 600 } }).explain("executionStats")

➢ Create an Index on category and price
>db.products.createIndex({ category: 1, price: 1 })

➢ Re-run the Same Query with explain( ) as follows
>db.products.find({ category: "Electronics", price: { $lt: 600 } }).explain("executionStats").
executionStats. totalDocsExamined



9 a. Develop a query to demonstrate Text search using catalog data collection
for a given word

➢ Create database
> use kannadaMovies
➢ Create collection
>db.createCollection(‘catalog’)


Create a Text Index

>db.catalog.createIndex({name: "text", genre: "text"})

➢ Add documents through the csv file consisting of multiple documents(using Adddata button in
mongoDB compass )

Verify the documents loaded for the collection
>db.catalog.countDocuments( ) // number of documents


Perform a Text Search Query

>db.catalog.find({$text: {$search: "maga"}}) //for name field

>db.catalog.find({$text: {$search: "comedy"}}) // for genre field

➢ Perform a Text Search Query for a phrase

>db.catalog.find({$text: {$search: ""tappida Maga""}})


9 b. Develop queries to illustrate excluding documents with certain words and
phrases

➢ Perform Queries to Exclude Documents

Example 1: Exclude Documents Containing the Word “action”

>db.catalog.find({ $text: { $search: "crime romance -action" }, year:2021 } )

Example 2: Exclude Documents Containing the Phrase “da maga”

> db.catalog.find({$text: {$search: 'maga -"da maga" '}})
or

>db.catalog.find({ $text: { $search: "maga -\\\"da maga\\\"" } })























