MongoDB Aggregation group with the sum of array object values from multiple document and count by group

1
votes

I have a MongoDB collection setup and it looks like this:

{
    "_id": "1",
    "TDL": {
        "name": "TDL 05",
        "TDLNumber": "031"
    },
    "POD": [{           
            "Volume": 35.40,
            "VolUnit": "m3"
        },
        {           
            "Volume": 20.75,
            "VolUnit": "m3"
        },
        {
            "Volume": 15,
            "VolUnit": "m3"
        }
    ]
},
{
    "_id": "2",
    "TDL": {
        "name": "Stock Watering",
        "TDLNumber": "01"
    },

    "POD": [{
            "Volume": 105,
            "VolUnit": "m3"
        },
        {
            "Volume": 70,
            "VolUnit": "m3"
        },
        {
            "Volume": 35,
            "VolUnit": "m3"
        }
    ]
},
{
    "_id": "3",
    "TDL": {
        "name": "TDL 30 Stock Water",
        "TDLNumber": "030"
    },

    "POD": [{
        "Volume": 87,
        "VolUnit": "m3"
    }]
},
{
    "_id": "4",
    "TDL": {
        "name": "TDL 30 Stock Water",
        "TDLNumber": "030"
    },
    "POD": [{
        "Volume": 25.12,
        "VolUnit": "m3"
    }]
},
{
    "_id": "5",
    "TDL": {
        "name": "TDL 05",
        "TDLNumber": "031"
    },
    "POD": [
        {
            "Volume": 21,
            "VolUnit": "m3"
        }
    ]
}

I want to write a query (C# Driver 2.0) that gives me a result, group by "TDL.TDLNumber" and sum of "POD.Volume". Find the below expected result.

{    
    "TDLNumber":"031",
    "GroupByDocCount": 2,
    "CumulativeVol": 92.15
},
{    
    "TDLNumber":"030",
    "GroupByDocCount": 2,
    "CumulativeVol": 112.12
},
{    
    "TDLNumber":"01",
    "GroupByDocCount": 1,
    "CumulativeVol": 210
}

However, I'm able to get the sum of "POD.Volume" group by but the count of "GroupByDocCount" is missmatch. Any help would be appreciate!!

2
mongodbmongodb-queryaggregation-frameworkmongodb-.net-driver

2 Answers

3
votes

Please find the below answer. This may not be optimal but working as expected

db.getCollection('test').aggregate([

{$group: {_id : "$TDL.TDLNumber", doc : {$push : "$POD"}}},
{$addFields: {"TDLNumber" : "$_id", "GroupByDocCount": {$size : "$doc"}, "Cumulative": {
          $reduce: {
            input: "$doc",
            initialValue: [ ],
            in: { $concatArrays: [ "$$value", "$$this" ] }
          }
        }}},
  {$project:{"TDLNumber" : 1, "GroupByDocCount" : 1, "CumulativeVal" : {$sum : "$Cumulative.Volume"}}}

])
0
votes

I have the same issue like this and follow the following approach hope you get the expected result. 

{
    $group: {
                _id: { activityName: '$activityName', groupName: '$item.groupName' },
                item: {
                    $push: '$item'
                }
            }
        },
        {
            $group: {
                _id: "$_id.activityName",
                group: {
                    $push: {
                        groupName: "$_id.groupName",
                        item: "$item",
                    }
                }
            }
        },