MongoDB - Aggregation Pipeline

Create the employee collection as shown below

 
  db.employee.insert({name:"harini",address:"vellore",salary:10000});
  db.employee.insert({name:"jeff",address:"goa",salary:9000});
  db.employee.insert({name:"mohan",address:"goa",salary:150000});
  db.employee.insert({name:"chris",address:"goa",salary:4000});
  db.employee.insert({name:"tim",address:"us",salary:5000});
  db.employee.insert({name:"david",address:"us",salary:11000});
  db.employee.insert({name:"satish",address:"vellore",salary:30000});

Match employees by name satish

 
  db.employee.aggregate({$match:{name:"satish"}})

Match employees who get a salary = 30000 and name is satish

 
  db.employee.aggregate(
  {$match:{$and:[{salary:{$eq:30000}},{name:{$eq:"satish"}}]}})

Project only name and salary by using $project

 
  db.employee.aggregate({$project:{name:1,salary:1}})

Supress object id and display the name and salary

 
  db.employee.aggregate({$project:{name:1,salary:1,_id:0}})

Project name and salary of all employees who get a salary greater than 10000

 
  db.employee.aggregate(
  {$match:{salary:{$gt:10000}}},
  {$project:{name:1,salary:1,_id:0}})

Find sum(salary) for all employees grouped by address

 
  select address,designation,sum(salary) as total_salary from employee group by address,designation;

Copy one collection to another collection

 
  db.employee.aggregate({$out:"employee4"});

Insert the following documents were added to Employee1 Collection

 
  
  {
    name: 'satish',
    address: 'vellore',
    salary: 30000,
    designation: 'Professor'
  },
  {
    name: 'harini',
    address: 'vellore',
    salary: 10000,
    designation: 'Professor'
  },
  {
    name: 'jeff',
    address: 'goa',
    salary: 9000,
    designation: 'Professor'
  },
  {
    name: 'mohan',
    address: 'goa',
    salary: 150000,
    designation: 'Professor'
  },
  {
    name: 'chris',
    address: 'goa',
    salary: 4000,
    designation: 'Associate Professor'
  },
  {
    name: 'tim',
    address: 'us',
    salary: 5000,
    designation: 'Associate Professor'
  },
  {
    name: 'david',
    address: 'us',
    salary: 11000,
    designation: 'Associate Professor'
  }

Display the total salary grouped by address and designation from the Employee1 collection

db.employee1.aggregate({ $project: { address: 1, designation: 1, salary: 1 } },{$group:{_id:{address:"$address",designation:"$designation"},total_salary:{$sum:"$salary"}}});

Display total employees for each address from the Employee Collection

 
  db.employee.aggregate({$group:{_id:"$address",total_employees:{$sum:1}}})

Write the address and total count of employees from vellore to a collection by name vellore_count

 
  db.employee.aggregate({$match:{address:"vellore"}},{$project:{address:1,salary:1}},{$group:{"_id":"$address",total_count:{$sum:1}}},{$out:"vellore_count"})

Write the total count of employees who are from goa and who earn a salary greater than 100000 and less than 200000 to a collection by name goa_details

 
  db.employee.aggregate({ $match: { $and: [{ address: { $eq: "goa" } }, { salary: { $gt: 100000 } }, { salary: { $lt: 200000 } }] } },
  { $project: { address: 1, salary: 1 } },{ $group: { _id: "$address", total_count: { $sum: 1 } } }, 
  { $out: "goa_details1" })

  or the below solution also works

  db.employee.aggregate({ $match: { $and: [{ address: { $eq: "goa" } },{ salary: { $gt: 100000 } }, { salary: { $lt: 200000 } }] } }, 
  {$count: "total_employees" },{ $out: "goa_details1" })

Write the address and maximum salary of all employees from each city to a collection by name highestsalary

 
  db.employee.aggregate(
  {$project:{address:1,salary:1}},{$group:{_id:"$address",max_salary:{$max:"$salary"}}},
  {$out:"highestsalary"})

Display the total salary in the ascending order for each address

 
  db.employee.aggregate( { $project: { address: 1, salary: 1 } },{ $group: { _id: "$address", total_Salary: { $sum: "$salary" } } },
  { $sort: { total_Salary: 1 } })

Display the total salary in the descending order for each address

 
  db.employee.aggregate(
  {$project:{address:1,salary:1}},{$group:{_id:"$address",total_Salary:{$sum:"$salary"}}},
  {$sort:{total_Salary:-1}})

Write the first 2 documents of employee collection to a collection by name test collection

 
  db.employee.aggregate({$limit:2},{$out:"testcollection"})

Write all documents of employee collection to a collection by name test collection by skipping the first 2

 
  db.employee.aggregate({$skip:2},{$out:"testcoll"})

Count the number of records

 
  db.employee.aggregate({$skip:2},{$count:"totalrecords"})

Count number of records in a table

 
  db.employee.find().count();

Demo on use of unwind command in MongoDB

Create the collection as shown below

 
  db.student1.insert({ "name" : "ram", "marks" : [ 25, 35, 45, 12, 9 ] });
  db.student1.insert({ "name" : "harini", "marks" : [ 95, 15, 25, 67, 99 ]});
  db.student1.insert({ "name" : "ramya", "marks" : [ 12, 14, 1, 22, 33 ]});
  db.student1.insert({ "name" : "chris", "marks" : [ 54, 29, 46 ]});

unwind example

 
  db.student1.aggregate({$unwind:"$marks"});

Find the sum of marks for each student

 
  db.student1.aggregate({$unwind:"$marks"},{$group:{_id:"$name",total_marks:{$sum:"$marks"}}});

Find the max marks scored by each student

 
  db.student1.aggregate({$unwind:"$marks"},{$group:{_id:"$name",max_marks:{$max:"$marks"}}});

Find the Average Marks scored by each student

 
  db.student1.aggregate({$unwind:"$marks"}, {$group:{_id:"$name",avg_marks:{$avg:"$marks"}}});

Find the Total number of subjects each student has appeared

 
  db.student1.aggregate({$unwind:"$marks"}, {$group:{_id:"$name",count_marks:{$sum:1}}});

Fetch the name and last subject mark for every student

 
  db.student1.aggregate( {$unwind:"$marks"},{$group:{_id:"$name",last_subject:{$last:"$marks"}}});

Fetch the name and first subject mark for every student

 
  db.student1.aggregate( {$unwind:"$marks"},{$group:{_id:"$name",last_subject:{$first:"$marks"}}});

Display the average marks for the last subject for all students

 
  db.student1.aggregate( 
  {$unwind:"$marks"},{$group:{_id:"$name",last_subject:{$last:"$marks"}}},{$group:{_id:"last_subject",avg_marks:{$avg:"$last_subject"}}})

Find the mark at a specific location in the array

 
  db.student1.aggregate({$project:{name:1,element3:{$arrayElemAt:["$marks",2]}}});

Find the average marks of the third subject for all students in the array

 
  db.student1.aggregate({$project:{name:1,element3:{$arrayElemAt:["$marks",2]}}},{$group:{_id:"element3",avg_marks:{$avg:"$element3"}}});