MongoDB - aggregation - How to group, concatenate arrays, sum integer field in a single step

Question

I have a collection called 'session_list' with the following rows.

{"user_id":"[email protected]","focus_score":[1, 2, 3, 4],"active_score":[3, 4, 1], "score" : 10}
{"user_id":"[email protected]","focus_score":[3, 4],"active_score":[3, 4, 1, 7, 7], "score" : 3}
{"user_id":"[email protected]","focus_score":[1, 2, 3, 4, 7],"active_score":[3, 9, 2], "score" : 7}
{"user_id":"[email protected]","focus_score":[5, 7, 8],"active_score":[1, 3, 7], "score" : 4}

How do I group by user_id and consolidate the focus_score array and then active_score array (without having any for loops) ?

Expected result:

{"user_id":"[email protected]","focus_score":[1, 2, 3, 4, 1, 2, 3, 4, 7], "active_score":[3, 4, 1, 3, 9, 2], "score_sum" : 17}
{"user_id":"[email protected]","focus_score":[3, 4, 5, 7, 8], "active_score":[3, 4, 1, 7, 7, 1, 3, 7], "score_sum" : 7}

My code:

db.session_list.aggregate([
        {
            $group: {
              _id:{user_id:'$user_id'},
              focus_score:{$push:'$focus_score'}
              active_score:{$push:'$active_score'}
              score_sum:{$sum:'$score_sum'}
            }
        }
    ])

But this does not provide the expected result.

YuTing · Accepted Answer · 2022-03-30 08:29:08Z

1

db.collection.aggregate([
  {
    $group: {
      _id: "$user_id",
      focus_score: { $push: "$focus_score" },
      active_score: { $push: "$active_score" },
      score_sum: { $sum: "$score" }
    }
  },
  {
    $project: {
      _id: 0,
      score_sum: 1,
      user_id: "$_id",
      focus_score: {
        $reduce: {
          input: "$focus_score",
          initialValue: [],
          in: { $concatArrays: [ "$$value", "$$this" ] }
        }
      },
      active_score: {
        $reduce: {
          input: "$active_score",
          initialValue: [],
          in: { $concatArrays: [ "$$value", "$$this" ] }
        }
      }
    }
  }
])

mongoplayground

answered Mar 30, 2022 at 8:29

YuTing

6,6692 gold badges8 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MongoDB - aggregation - How to group, concatenate arrays, sum integer field in a single step

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related