CODEWITHSUNDEEP
arraysmongodbmongodb-queryaggregation-framework
YulePale
YulePale

Reputation: 7706

MongoDB Aggregation: How to group an array of objects get the multiple of the groups then sum the multiples

documents

[    
    { _id: "a1", 
      selections:[
          {_id:"s1", questionId:"q1", points:3, group:"no-group"},
          {_id:"s2", questionId:"q2", points:2, group:"group-1"},
          {_id:"s3", questionId:"q3", points:3, group:"no-group"},
          {_id:"s4", questionId:"q4", points:7, group:"group-2"},
          {_id:"s5", questionId:"q5", points:8, group:"group-2"},
          {_id:"s6", questionId:"q6", points:9, group:"group-1"},
      ],
      userId: "u1"
    },
]

I am trying to create an aggregation that can group an array of objects get the multiple of the groups then sum the multiples. For example for the given document above the grouped array would look like below:

[           
    [
          {_id:"s1", questionId:"q1", points:3, group:"no-group"},
          {_id:"s3", questionId:"q3", points:3, group:"no-group"},
    ],
    [
          {_id:"s2", questionId:"q2", points:2, group:"group-1"},
          {_id:"s6", questionId:"q6", points:9, group:"group-1"},
    ],
    [
          {_id:"s4", questionId:"q4", points:7, group:"group-2"},
          {_id:"s5", questionId:"q5", points:8, group:"group-2"},
    ],
]

Then get multiples from all groups except the ones with group: "no-group". The ones for "no-group" sum them instead of getting their multiple.

For example the grouped array would result to:

[
    [3+3],// for no-group sum the points
    [2*9],// for group-1 multiply the points
    [7*8],// for group-2 multiply the points
]
// output = [ 6, 18, 56 ]

Then sum the output 6+18+56=80.

How can I do this in mongodb aggregation? So I can get an output like

{ userId:"u1", totalPoints:"80" }

Upvotes: 0

Views: 3702

Answers (1)

mitsos1os
mitsos1os

Reputation: 2290

How about this?

db.getCollection('col1').aggregate([
    { $unwind: '$selections' }, // unwrap initial selections array
    { $group: { _id: { user: '$userId', group: '$selections.group' }, points: { $push: '$selections.points' } } }, // group by user and group
    { $group: { _id: '$_id.user', totalPoints: { $sum: { // get the total sum for...
        $cond:{
            if: { $eq: ['$_id.group', 'no-group'] },
            then: { $sum: '$points'}, // the sum of points if no-group
            else: { $reduce: { input: '$points', initialValue: 1, in: { $multiply: ['$$value', '$$this']} } } // the multiplication of points for rest of cases
        }
    } } } }
])

Upvotes: 3

Related Questions