Contents of page >
1) Let's create new collection and insert document in it before finding - before Using HAVING clause like sql in MongoDB >
db.employee.insert({_id : 1, firstName:"ank", salary : 1000 })
db.employee.insert({_id : 2, firstName:"ank", salary : 2000 })
db.employee.insert({_id : 3, firstName:"sag", salary : 3000 })
db.employee.insert({_id : 4, firstName:"sag", salary : 4000 })
db.employee.insert({_id : 5, firstName:"neh", salary : 5000 })
|
Above will create collection (or table) (if collection already exists it will insert documents in it).
FIND > Query all documents of collection using find() method>
db.employee.find()
|
Output>
{ "_id" : 1, "firstName" : "ank", "salary" : 1000 }
{ "_id" : 2, "firstName" : "ank", "salary" : 2000 }
{ "_id" : 3, "firstName" : "sag", "salary" : 3000 }
{ "_id" : 4, "firstName" : "sag", "salary" : 4000 }
{ "_id" : 5, "firstName" : "neh", "salary" : 5000 }
|
2) Find sum of salary of all employee with same firstName in collection in MongoDB >
Step 1 - We will group employee by firstName and find sum of salary of each group.
Step 2- We will use aggregate() method, $sum operator and $group operator.
db.employee.aggregate([{$group : {_id : "$firstName", salary_sum : {$sum : "$salary"}}}])
|
Output >
{ "_id" : "ank", "salary_sum" : 3000 }
{ "_id" : "sag", "salary_sum" : 7000 }
{ "_id" : "neh", "salary_sum" : 5000 }
|
Sql query equivalent to above MongoDB query is >
select firstName, sum(salary) salary_sum from employee group by firstName;
|
3) How to write aggregate query with HAVING clause like SQL in MongoDB >
Find sum of salary of all employee with same firstName having sum_of_salary > 3000 in collection in MongoDB >
Step 1 - We will use aggregate() method, $group operator to group employee by firstName and find sum of salary of each group.
Step 2 - We will use $match operator to find result document having sum_of_salary > 3000
db.employee.aggregate([
{$group : {_id : "$firstName", salary_sum : {$sum : "$salary"}}},
{ $match: { salary_sum : { $gt: 3000} } }])
|
Output >
{ "_id" : "sag", "salary_sum" : 7000 }
{ "_id" : "neh", "salary_sum" : 5000 }
|
Sql query equivalent to above MongoDB query is >
select firstName, sum(salary) salary_sum from employee
group by firstName
having sum(salary) > 3000
|
4) How to write aggregate query with WHERE clause in MongoDB >
Find sum of salary of all employee with same firstName where salary > 2000 in collection in MongoDB >
Step 1 - We will find document where salary > 2000 by using $match operator
Step 3 - Then we will group employee by firstName and find sum of salary of each group. By using aggregate() method, $group operator
db.employee.aggregate([
{ $match: { salary : { $gt: 3000} } },
{$group : {_id : "$firstName", salary_sum : {$sum : "$salary"}}}])
|
Output >
{ "_id" : "sag", "salary_sum" : 4000 }
{ "_id" : "neh", "salary_sum" : 5000 }
|
Sql query equivalent to above MongoDB query is >
select firstName, sum(salary) salary_sum from employee
where salary > 3000
group by firstName
|
5) Summary -
So in this MongoDB tutorial we learned to use having clause like sql in MongoDB.
Find sum of salary of all employee with same firstName in collection in MongoDB >
db.employee.aggregate([{$group : {_id : "$firstName", salary_sum : {$sum : "$salary"}}}])
|
How to write aggregate query with HAVING clause like SQL in MongoDB >
Find sum of salary of all employee with same firstName having sum_of_salary > 3000 in collection in MongoDB >
db.employee.aggregate([
{$group : {_id : "$firstName", salary_sum : {$sum : "$salary"}}},
{ $match: { salary_sum : { $gt: 3000} } }])
|
How to write aggregate query with WHERE clause in MongoDB >
Find sum of salary of all employee with same firstName where salary > 2000 in collection in MongoDB >
db.employee.aggregate([
{ $match: { salary : { $gt: 3000} } },
{$group : {_id : "$firstName", salary_sum : {$sum : "$salary"}}}])
|
Having any doubt? or you liked the tutorial! Please comment in below section.
Please express your love by liking JavaMadeSoEasy.com (JMSE) on facebook, following on google+ or Twitter. You may join our fbGroup or linkedInGroup as well.
RELATED LINKS>
What is MongoDB - A quick introduction to database
Labels:
MongoDB