CODEWITHSUNDEEP
node.jsmongodbmongoose
Cam.phiefr
Cam.phiefr

Reputation: 97

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 ?

Upvotes: 0

Views: 7431

Answers (1)

4J41
4J41

Reputation: 5095

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":"countryList","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 }

Upvotes: 4

Related Questions