CODEWITHSUNDEEP
mongodbaggregation-framework
Konsta
Konsta

Reputation: 85

Aggregation $group $sum fields among one document

Here is document example, year field contains year keys, that contains some metrics with included days as keys:

{ 
"_id" : NumberInt(1), 
"year" : {
    "2017" : {
        "g1" : {
            "1" : {
                "total" : 2.0
            }, 
            "2" : {
                "total" : 5.0
            }
        }, 
        "g2" : {
            "1" : {
                "total" : 3.0
            }, 
            "2" : {
                "total" : 6.0
            }
        }
    }
}

I dont want getting document into memory to summarize total field for each key field g#.

How could i say to mongodb, summ total field for each key in year field.

Result that i want: g1 = 7.0, g2 = 9.0

Upvotes: 0

Views: 930

Answers (1)

s7vr
s7vr

Reputation: 75984

You have to change your year part of structure to something like below.(Preferred)

"year" : [{ "k" : "2017", "v":[{ "k": "g1", "v":[{ "k" : "1","v" : {"total" : 2 }},{ "k" : "2","v" : {"total" : 5}}]}, { "k": "g2", "v":[{ "k" : "1","v" : {"total" : 3 }},{ "k" : "2","v" : {"total" : 6}}]}]}]

You can the below aggregation. This will work without knowing the keys ahead of time.

The query $unwinds couple of times to reach the g & total document followed by group on the g key and calculate total sum.

db.collection.aggregate([
 {$match:{_id:1}},
 {$unwind:"$year"},
 {$unwind:"$year.v"},
 {$unwind:"$year.v.v"},
 {
  $group: 
    {
      _id:"$year.v.k",
      sum: {$sum:"$year.v.v.v.total"}   
    }
 }
 ])

This is the solution if you can't change your structure.

You can use 3.4.4 version and use $objectToArray to convert all the dynamic keys into labeled key and value pair.

Stage 1 & 2: Match on _id filter and convert the dynamic year keys into label value pair.

Stage 3 & 4: $unwind year array & $reduce the total value to calculate sum before changing the g1 and g2 dynamic keys to labeled key and value pair.

db.collection.aggregate([
 {$match:{_id:1}},
 {$addFields: {"year": {$objectToArray: "$year"}}},
 {$unwind:"$year"},
 {
  $project: 
    {
      g1: 
        {
             $reduce: {
                input: {$objectToArray: "$year.v.g1"},
                initialValue: 0,
                in: { $sum: [ "$$value", "$$this.v.total" ] }
            }
        },
      g2: 
        {
             $reduce: {
                input: {$objectToArray: "$year.v.g2"},
                initialValue: 0,
                in: { $sum: [ "$$value", "$$this.v.total" ] }
            }
        }
    }
 }
])

Upvotes: 1

Related Questions