MongoDB Part2
1.
Aggregations Exercise in MongoDB-I
AIM: To demonstrate
Aggregations in MongoDB
THEORY:
Aggregations operations process data records
and return computed results. Aggregation operations group values from multiple
documents together, and can perform a variety of operations on the grouped data
to return a single result. MongoDB provides three ways to perform aggregation:
the aggregation pipeline, the map-reduce function, and single purpose
aggregation methods.
Syntax
db.collection.aggregate(pipeline,
options)
Parameters
Parameter Details
1. pipeline
array(A sequence of data aggregation operations or stages)
2. options
document(optional, available only if pipeline present as an array)
PROGRAM:
Example: Insert following documents in
transcations collection
>db.transactions.insert({ cr_dr :
"D", amount : 100, fee : 2});
>db.transactions.insert({ cr_dr :
"C", amount : 100, fee : 2});
>db.transactions.insert({ cr_dr :
"C", amount : 10, fee : 2});
>db.transactions.insert({ cr_dr :
"D", amount : 100, fee : 4});
>db.transactions.insert({ cr_dr :
"D", amount : 10, fee : 2});
>db.transactions.insert({ cr_dr :
"C", amount : 10, fee : 4});
>db.transactions.insert({ cr_dr :
"D", amount : 100, fee : 2});
Count
How do you get the number of Debit and Credit
transactions? One way to do it is by using count() function as below.
>db.transactions.count({cr_db:'D'})
O/p: 4
>db.transactions.count({cr_db:'C'})
O/p: 3
But what if you do not know the possible
values of cr_db upfront. Here Aggregation framework comes to play.
>db.transactions.aggregate( [ { $group: {
_id:'$cr_db', count: { $sum: 1 } } }])
O/p: :[ { _id: 'C', count: 3 }, { _id: 'D',
count: 4 } ]
To get the summation of amount
>db.transactions.aggregate( [ { $group: {
_id:'$cr_db', count: { $sum: 1 }, totalAmount:{$sum:'$amount'} } }])
O/p: :[
{
_id: 'C', count: 3, totalAmount: 120 },
{
_id: 'D', count: 4, totalAmount: 310 }
]
Adding Amount and fee by group of CR_DB
>db.transactions.aggregate({$group: { _id:
'$cr_db', count: {$sum:1}, TotalAmount:{$sum:{ $sum: ['$amount','$fee']}}}})
[
{ _id: 'C', count: 3, TotalAmount: 128 },
{ _id: 'D', count: 4, TotalAmount: 320 }
]
2.
Aggregations Exercise II in MongoDB
AIM: To demonstrate Aggregations with Match and
Group stages
PROGRAM:
Sample Data:
db.employees.insertMany([
{
_id:1,
firstName: "John",
lastName: "King",
gender:'male',
email:
"john.king@abc.com",
salary:
5000,
department:
{
"name":"HR"
}
},
{
_id:2,
firstName: "Sachin",
lastName: "T",
gender:'male',
email:
"sachin.t@abc.com",
salary:
8000,
department:
{
"name":"Finance"
}
},
{
_id:3,
firstName: "James",
lastName: "Bond",
gender:'male',
email:
"jamesb@abc.com",
salary:
7500,
department:
{
"name":"Marketing"
}
},
{
_id:4,
firstName: "Rosy",
lastName: "Brown",
gender:'female',
email:
"rosyb@abc.com",
salary:
5000,
department:
{
"name":"HR"
}
},
{
_id:5,
firstName: "Kapil",
lastName: "D",
gender:'male',
email:
"kapil.d@abc.com",
salary:
4500,
department:
{
"name":"Finance"
}
},
{
_id:6,
firstName: "Amitabh",
lastName: "B",
gender:'male',
email:
"amitabh.b@abc.com",
salary:
7000,
department:
{
"name":"Marketing"
}
}
])
$match Stage
The $match stage
is usually the first stage to select only the matching documents from a
collection. It is equivalent to the Find() method.
db.employees.aggregate([{$match:{gender:'female'}}])
Output
[
{
_id: 4,
firstName: 'Rosy',
lastName: 'Brown',
gender: 'female',
email: 'rosyb@abc.com',
salary: 5000,
department: { name: 'HR' }
}
]
The $match stage
in the aggregate() method
gives the same output as the find() method.
The db.persons.find({ gender: 'female' }) would
return the same data as above.
$group Stage
Use the $group stage
to group the input documents by the specified _id expression and returns a single document containing
the accumulated values for each distinct group. Consider the following example.
Example: $group Stage
Copy
db.employees.aggregate([
{ $group:{ _id:'$department.name'} }
])
Output
[ { _id: 'Marketing' }, { _id: 'HR' }, { _id: 'Finance' } ]
The $match stage
in the aggregate() method
gives the same output as the find() method.
The db.persons.find({ gender: 'female' }) would
return the same data as above.
In the above example, only the $group stage is specified in the
pipeline array. The $group uses _id field to calculate the accumulated
values for all the input documents as a whole. The expression { _id:'$department.name'} creates the
distinct group on the field $department.name.
Since we don't calculate any accumulated values, it returns the distinct values
of $department.name, as shown
below.
Now, let's calculate the accumulated values
for each group. The following calculates the number of employees in each
department.
Example: Get Accumulated
Values
Copy
db.employees.aggregate([
{ $group:{ _id:'$department.name', totalEmployees: { $sum:1 } }
}])
Output
[
{ _id: 'Marketing', totalEmployees: 2 },
{ _id: 'HR', totalEmployees: 2 },
{ _id: 'Finance', totalEmployees: 2 }
]
In the above example, we create distinct
groups using _id:'$department.name' expression.
In the second expression totalEmployees:
{ $sum:1 }, the totalEmployees is
a field that will be included in the output, and { $sum:1 } is an accumulator expression where $sum is an Accumulator Operator that returns a sum of numerical values.
Here, { $sum:1 } adds 1 for
each document that falls under the same group.
The following aggregation pipeline contains
two stages.
Example: $match and $group
Copy
db.employees.aggregate([
{ $match:{ gender:'male'}},
{ $group:{ _id:'$department.name', totalEmployees: { $sum:1 } }
}])
Output
[
{ _id: 'Marketing', totalEmployees: 2 },
{ _id: 'HR', totalEmployees: 1 },
{ _id: 'Finance', totalEmployees: 2 }
]
In the above example, the first stage selects
all male employees and passes them as input to the second stage $group as an input. So, the output
calculates the sum of all male employees.
The following calculates the sum of salaries
of all male employees in the same department.
Example: Get Sum of Fields
Copy
db.employees.aggregate([
{ $match:{ gender:'male'}},
{ $group:{ _id:{ deptName:'$department.name'}, totalSalaries: {
$sum:'$salary'} }
}])
Output
[
{ _id: 'Finance', totalSalaries: 12500 },
{ _id: 'HR', totalSalaries: 10000 },
{ _id: 'Marketing', totalSalaries: 14500 }
]
In the above example, { $match:{ gender:'male'}} returns all
male employees. In the $group stage,
an accumulator expression totalSalaries:
{ $sum:'$salary'} sums up numeric field salary and include it as totalSalaries in the output for each
group.
3. Use Of Operators in
CRUD in MongoDB
AIM: To demonstrate use of various Operators in MongoDB
THEORY:
In MongoDB, operators are used to
perform various operations on data. Here are some examples of operators
commonly used in MongoDB:
Comparison Operators:
$eq: Matches values that are equal to a specified value.
$ne: Matches values that are not equal to a specified value.
$gt: Matches values that are greater than a specified value.
$lt: Matches values that are less than a specified value.
$gte: Matches values that are greater than or equal to a specified
value.
$lte: Matches values that are less than or equal to a specified value.
Logical Operators:
$and: Joins query clauses with a logical AND.
$or: Joins query clauses with a logical OR.
$not: Inverts the effect of a query expression.
Array Operators:
$in: Matches any of the values specified in an array.
$all: Matches arrays that contain all the specified elements.
$size: Matches arrays with a specific number of elements.
Array Update Operators:
$push: Appends a value to an array field.
$pull: Removes all instances of a value from an array field.
$addToSet: Adds a value to an array field if it does not already exist.
$pop: Removes the first or last element of an array field.
These are just a few
examples of the operators available in MongoDB. MongoDB provides a rich set of
operators to query and manipulate data efficiently.
PROGRAM:
Create users collection with name, age
// Find documents where the age is greater than 30
db.users.find({ age: { $gt: 30 } });
// Find documents where the name is not equal to
"John"
db.users.find({ name: { $ne: "John" } });
// Find documents where the age is greater than 30 AND
the name is "John"
db.users.find({ $and: [{ age: { $gt: 30 } }, { name:
"John" }] });
// Find documents where the age is less than 25 OR the
name is "Jane"
db.users.find({ $or: [{ age: { $lt: 25 } }, { name:
"Jane" }] });
Create articles collection with tags fields
// Find documents where the "tags" field
contains either "mongodb" or "database"
db.articles.find({ tags: { $in: ["mongodb",
"database"] } });
Create students collection with scores in subjects
// Find documents where the "scores" array has
exactly 3 elements
db.students.find({ scores: { $size: 3 } });
Array Update operators
Create required collections with documents before.
// Add a new element to the "tags" array field
db.articles.updateOne({ _id:
ObjectId("articleId") }, { $push: { tags: "mongodb" } });
// Remove all occurrences of the value
"completed" from the "status" array field
db.tasks.updateMany({}, { $pull: { status:
"completed" } });
// Add the value "John" to the
"names" array field if it doesn't exist
db.users.updateOne({ _id: ObjectId("userId") },
{ $addToSet: { names: "John" } });
// Remove the last element from the "scores"
array field
db.students.updateOne({ _id:
ObjectId("studentId") }, { $pop: { scores: 1 } });
// Remove the first element from the "scores"
array field
db.students.updateOne({ _id:
ObjectId("studentId") }, { $pop: { scores: -1 } });
These commands demonstrate how to use the array update operators in
MongoDB to modify array fields within documents. Remember to replace the
placeholders like "articleId," "userId," and
"studentId" with the actual document IDs or appropriate query
conditions.
4. Index Management in
MongoDB
AIM: To
demonstrate Index Management MongoDB
THEORY:
Indexes support the efficient resolution of
queries. Without indexes, MongoDB must scan every document of a collection to
select those documents that match the query statement. This scan is highly
inefficient and require MongoDB to process a large volume of data.
Indexes are special data structures, that
store a small portion of the data set in an easy-to-traverse form. The index
stores the value of a specific field or set of fields, ordered by the value of
the field as specified in the index.
PROCEDURE:
The createIndex() Method
To create an index, you
need to use createIndex() method of MongoDB.
Syntax
The basic syntax of createIndex() method
is as follows
>db.COLLECTION_NAME.createIndex({name:1})
Here key is the name of the
field on which you want to create index and 1 is for ascending order. To create
index in descending order you need to use -1.
In createIndex() method
you can pass multiple fields, to create index on multiple fields.
The dropIndex() method
You can drop a particular index using the
dropIndex() method of MongoDB.
Syntax
The basic syntax of DropIndex() method is as
follows().
>db.COLLECTION_NAME.dropIndex({name:1})
PROGRAM:
Example
of CreateIndex
>db.mycol.cretaeIndex({"title":1})
{
"createdCOllectionAUtomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
Example of CreateIndex (MultiField Index)
>db.mycol.createIndex({"title":1,"description':-1})
use >db.col.getIndexes() to list out all
Indexes in the collection
Example
of dropIndex
>db.mycol.dropIndex({"title":1})
5. CRUD Operations in MONGODB from
Python Exercise–I: Sports database
AIM: To
insert Sports Data in MongoDB from Python Program using Pymongo Drivers
THEORY:
PyMongo is a Python
library that allows you to interact with MongoDB, a popular NoSQL database.
PyMongo provides a convenient and intuitive way to work with MongoDB by
translating Python code into database operations.
PROGRAM:
In Jupyter Cell execute ‘pip install pymongo’
After execution Restart Kernel
Write following Code in Jupyter and execute
import pymongo
url='mongodb://localhost:27017/'
client=pymongo.MongoClient(url)
db1=client['sports']
col1=db1['col11']
doc1={'name':'cricket','level':'international','no':11}
col1.insert_one(doc1)
doc2={'name':'hockey','level':'national','no':10}
col1.insert_one(doc2)
doc3={'name':'FootBall','level':'international','no':10}
col1.insert_one(doc3)
#Inserting documents with
embedded documents with array fields
import pymongo
url='mongodb://localhost:27017/'
client=pymongo.MongoClient(url)
db1=client['sports']
col4 = db1['cricket']
doc4={'name':'cricket','level':'ipl','no':11,'players':{'batsmen':['virat','dhoni','rohit','kl
rahul','sky'],'bowlers':['shami','kuldeep','ashwin','jadeja','bumrah'],'wicketkeeper':'dhoni'}}
col4.insert_one(doc4)
Now Open MongoShell or Compass and test the
data
6.
CRUD Operations in MONGODB from
Python Exercise–II:
Fruits Database
AIM: To insert Sports Data in MongoDB from Python Program using Pymongo
Drivers
THEORY:
PyMongo is a Python
library that allows you to interact with MongoDB, a popular NoSQL database.
PyMongo provides a convenient and intuitive way to work with MongoDB by
translating Python code into database operations.
PROGRAM:
In Jupyter Cell execute ‘pip install pymongo’
After execution Restart Kernel
Write following Code in Jupyter and execute
import pymongo
# Connect to MongoDB server
client = pymongo.MongoClient("mongodb://localhost:27017/")
# Create the Fruits database and New collection
db = client["Fruits"]
collection = db["New"]
# Define the documents to insert
document1 = {
"name":
"banana",
"color":
"yellow",
"no": 5,
"price": 10
}
document2 = {
"name":
"grape",
"color":
"dark blue",
"no": 100,
"price": 2
}
# Insert the documents into the collection
collection.insert_many([document1, document2])
# Create the Countries database and New1 collection
db = client["Countries"]
collection = db["New1"]
# Define the documents to insert
document1 = {
"name":
"India",
"capital":
"delhi",
"no_states": 28
}
document2 = {
"name":
"USA",
"capital":
"Washington DC",
"no_states": 50
}
# Insert the documents into the collection
collection.insert_many([document1, document2])
To test data in MongoDB, open
Mongoshell or compass and check data
Comments
Post a Comment