MongoDB Lab3
1. If you want to check your databases list, use the command show dbs
O/p:
test> show dbs
admin 40.00 KiB
config 60.00 KiB
employees 56.00 KiB
local 72.00 KiB
student 72.00 KiB
test 112.00 KiB
2. If you want to delete new database mydb, then dropDatabase() command would be as follows:
test> use mydb
switched to dbmydb
mydb>db.dropDatabase()
{ ok: 1, dropped: 'mydb' }
Use showdbs to check
3. To display collections in current database
student> show collections
col1
Aggregations
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)
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 }
]
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.
$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' } ]
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.
Comments
Post a Comment