CODEWITHSUNDEEP
c#mongodb
Chee Mun
Chee Mun

Reputation: 85

Mongo Group By SUM

I have the following records:

{
"_id" : ObjectId("5a0108d11b4af44a24d36e72"),
"Country" : "MY",
"Company_ID" : "MY01",
"Staff_ID" : "NBJ64",
"Calculation" : {
    "CustSatisfaction" : [ 
        {
            "Trans_Year" : 2017,
            "Trans_Month" : 7,
            "HH" : 10,
            "HN" : 0,
            "NH" : 4,
            "NN" : 0
        }, 
        {
            "Trans_Year" : 2017,
            "Trans_Month" : 7,
            "HH" : 1,
            "HN" : 0,
            "NH" : 5,
            "NN" : 0
        }
    ]
}
  }

I tried to get the SUM result for HH, HN, NH, NN like below to group by Trans_Year and Trans_Month:

{
"_id" : ObjectId("5a0108d11b4af44a24d36e72"),
"Country" : "MY",
"Company_ID" : "MY01",
"Staff_ID" : "NBJ64",
"Calculation" : {
    "CustSatisfaction" : [ 
        {
            "Trans_Year" : 2017,
            "Trans_Month" : 7,
            "HH" : 11,
            "HN" : 0,
            "NH" : 9,
            "NN" : 0
        }
    ]
}
  }

I have the following codes for my total up:

            var grouping = new BsonDocument {
        { "_id", new BsonDocument { { "build", "$WeCare.CustSatisfaction.Trans_Year" }, { "status", "$WeCare.CustSatisfaction.Trans_Month" } } },
        { "HH", new BsonDocument("$sum", 1)  }
    };
            var aggregate = mongoDB.Consultant.Aggregate().Group(grouping).ToList();

The result I get become {{ "_id" : { "build" : [2017, 2017], "status" : [7, 7] }, "HH" : 1 }}. This is very strange.

Upvotes: 0

Views: 599

Answers (1)

Buzz Moschetti
Buzz Moschetti

Reputation: 7568

OK, so it's essentially a 5-way group (Country, Company, Staff, and year and month inside the array). This should do the trick:

db.foo.aggregate([
{$unwind: "$Calculation.CustSatisfaction"} // important step!
,{$group: {_id: {country: "$Country",
             company: "$Company_ID",
             staff: "$Staff_ID",
             year: "$Calculation.CustSatisfaction.Trans_Year",
             mon: "$Calculation.CustSatisfaction.Trans_Month"},
       tHH: {$sum: "$Calculation.CustSatisfaction.HH"},
       tHN: {$sum: "$Calculation.CustSatisfaction.HN"},
       tNH: {$sum: "$Calculation.CustSatisfaction.NH"},
       tNN: {$sum: "$Calculation.CustSatisfaction.NN"}
}}
                ]);

Upvotes: 1

Related Questions