How To Use Multiple Aggregation In Mongo In Single Query
Posted By : Vipul Pandey | 28-Feb-2018
$facet allow us to get multiple aggregation pipeline result within a single stage of response of a query.Each pipeline(sub-aggregation) has own field and add the record in an array.
To userstand this lets take an example:
Here I am adding 5 enteries to a collection users with the Array (Managers) which will contains the _id's of Managers but where the managers are not defined or exist we are leaving them blank or null.
db.users.insertMany([
{ _id: 1, name: 'Vipul', Manager: [3,5] },
{ _id: 2, name: 'Maneesh'},
{ _id: 3, name: 'Rijul', Manager: [] },
{ _id: 4, name: 'Pulkit', Manager: [3] },
{ _id: 5, name: 'Anil', Manager: [3] }
]);
{ "acknowledged" : true, "insertedIds" : [ 1, 2, 3, 4, 5 ] }
To view All records :
db.users.find()
{ "_id" : 1, "name" : "Vipul", "Manager" : [ 3, 5 ] }
{ "_id" : 2, "name" : "Maneesh" }
{ "_id" : 3, "name" : "Rijul", "Manager" : [ 2 ] }
{ "_id" : 4, "name" : "Pulkit", "Manager" : [ 3 ] }
{ "_id" : 5, "name" : "Anil", "Manager" : [ 3 ] }
Now we are going to add a single aggregation which will fetch all the records and the name of there managers based on 2 criteria i.e on the basis of there id and on the basis of there name.
db.users.aggregate([
{"$unwind":{path:"$Manager"}},
{$lookup:{from:"users",localField:"Manager",foreignField:"_id",as :"mgr"}},
{$facet: {
"groupBy_ID": [ {$group:{_id:"$_id", data:{$push:"$mgr.name"}}}],
"groupByName":[ {$group:{_id:"$name",data:{$push:"$mgr.name"}}}]
}}
])
RESponse :
{
"groupBy_ID" : [
{
"_id" : 5,
"local" : [
[
"Rijul"
]
]
},
{
"_id" : 4,
"local" : [
[
"Rijul"
]
]
},
{
"_id" : 1,
"local" : [
[
"Rijul"
],
[
"Anil"
]
]
}
],
"groupByName" : [
{
"_id" : "Anil",
"local" : [
[
"Rijul"
]
]
},
{
"_id" : "Pulkit",
"local" : [
[
"Rijul"
]
]
},
{
"_id" : "Vipul",
"local" : [
[
"Rijul"
],
[
"Anil"
]
]
}
]
}
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
Vipul Pandey
Vipul Pandey is a good team-player & developing application on MEAN and java spring boot. Vipul loves to keep rotating fingers in his keyboard until he creates somethings inspiring.Hobbies are playing cricket ,swimming & photography.