CODEWITHSUNDEEP
mongodbaggregation-framework
Adarsh
Adarsh

Reputation: 3573

MongoDB aggregation without duplication

I have the following records:

{ "_id" : 1, "c" : 120, "b" : [ { "f1" : 10 }, { "f1" : 10 } ] }
{ "_id" :2, "c" : 5, "b" : [ { "f1" : 10 }, { "f1" : 10 } ] }

I need the output this way:

{ "_id" : 1, 'total':140}
{ "_id" :2, 'total':25 }

where total = sum of value in 'c' with sum of values in f1 for same record.

When i unwind the field 'b' it creates two documents with same id and hence data is duplicated and when i sum it up, i get:

db.test2.aggregate([ 
  {'$unwind':'$b'},
  {'$project':{'total':{'$add':['$c','$b.f1']}}},
  {'$group':{'_id':'$_id', 'total':{'$sum':'$total'}}}
])

outputs:

{ "_id" : 1, 'total':260}
{ "_id" :2, 'total':30 }

(not what i wanted, as it has added 120 and 5 again to total due to duplication during unwinding)

So i tried:

db.test2.aggregate([ 
   {'$unwind':'$b'},
   {'$group':{'_id':'$_id', 'c':{'$push': '$c'},'f1':{'$sum':'$b.f1'}}},
   {'$project':{'total':{'$add':[{'$arrayElemAt':['$c',0]},'$f1']}}}
])

outputs:

{ "_id" : 1, 'total':140}
{ "_id" :2, 'total':25 }

( what i wanted)

Is there any other way to achieve this?

Upvotes: 1

Views: 85

Answers (2)

s7vr
s7vr

Reputation: 75934

You can try below query. Sum operator to first calculate sum in array followed by add to calculate total with other field. 

db.test2.aggregate([{
  $project: {
       total: {"$add":["$c", {"$sum":"$b.f1"}]}
    }
}]

Upvotes: 2

Alex P.
Alex P.

Reputation: 3171

An alternative:

db.test2.aggregate([{
        $project: {
            _id: 0,
            c: "$c",
            b: {
                $reduce: {
                    input: "$b.f1",
                    initialValue: 0,
                    in: {
                        $add: ["$$value", "$$this"]
                    }
                }
            }
        }
    },
    {
        $project: {
            _id: 0,
            total: {
                $sum: ["$c", "$b"]
            }
        }
    }
])

That would create result:

{ 
    "total" : 140
}
{ 
    "total" : 25
}

If you need the field _id then replace the _id: 0 in both $project to _id: 1

That would create this result:

{ 
    "_id" : 1, 
    "total" : 140
}
{ 
    "_id" : 2, 
    "total" : 25
}

Upvotes: 1

Related Questions