Cam.phiefr Cam.phiefr - 2 months ago 41
Node.js Question

Mongodb lookup + group by with mongoose

I have two different mongoose collection as follow :

{ "_id" : 1, "countryId" : 1, "price" : 12, "quantity" : 24 }
{ "_id" : 2, "countryId" : 2, "price" : 20, "quantity" : 1 }
{ "_id" : 3 }
{ "_id" : 4, "countryId" : 1, "price" : 12, "quantity" : 24 }



{ "_id" : 1, "id" : 1, description: "Colombia"}
{ "_id" : 3, "id" : 2, description: "Mexic" }


I'm trying to aggregate them so that i can have a result as follow :

{"country":"Colombia","total":48}
{"country":"Mexic","total":1}


I've tried many things but it's always failing here is the last version of what i'm working on ( i've changed the data but you get the idea ) :

Model.aggregate([
{
$lookup:
{
from: "countryList",
localField: "countryId",
foreignField: "id",
as: "country"
},
{
$project: {
quantity:1, country:{$country:"$countryList.description"}
}
},{
$group:{
{ _id : null, qtyCountry: { $sum: "$quantity" } }
}
}
}],function (err, result) {
if (err) {
console.log(err);
} else {
console.log(result)
}
}
);


Is it even possible ?

Answer

Yes, it is possible. You can try the following aggregation pipeline.

var pipeline = [
                    {"$match":{"countryId":{"$exists":true}}},
                    {"$group" : {"_id":"$countryId", "quantity":{"$sum":"$quantity"}}}, 
                    {"$lookup":{"from":"country","localField":"_id", "foreignField":"id","as":"country"}}, 
                    {"$unwind":"$country"}, 
                    {"$project": {"country":"$country.description", "total":"$quantity", _id:0}}
                ]

Sample output:

{ "country" : "Mexic", "total" : 1 }
{ "country" : "Colombia", "total" : 48 }