MongoDB aggregate: multiple group + elements array

Question

The structure of the documents looks like this:

"_id" : ObjectId("581496e8564627c098e41755"),
"startdate": somedate,
"enddate": somedate,
"userId" : 1,
"activity" : "activity1",
"measures" : [ 
    {
        "M1" : 99,
        "M2" : 103,
        "M3" : 118,
        "M4" : 4
    }, 
    {
        "M1" : 136,
        "M2" : 89,
        "M3" : 108,
        "M4" : 6
    },

... and so on

There are 50 users, 8 types of activity, about 100 measures per activity. A user can have the same activity on another date with other measures. I have around 3000 documents in the db: 1 document per user per activty with measures.

I would like to get per user, per activity all the measures.

I have following code:

`db.armband.aggregate([
  {$match: { "measures.M1": { $gt: 1 } } },
  {$project: { _id: 0, userId: 1, activity:1, measures:1 } },
  {$sort: {userId:1, activity:1} },
  {$out: "actPerUser"}
  ])
`

Problem here is I get 1 document per activity with the measures in order. But I get:

1 document with userid1, activity1, measures 100
1 document with userid1, activity1, measures 100
1 document with userid2, activity1, measures 100

I would like to have one document: userid1, activity1, measures (all the measures for that activity - in example above it would be 200.)

I've then tried:

`db.armband.aggregate(
   [
     {
      $group:
     {
      _id: { userId: "$userId" },
       actMes: { $push:  { activity:"$activity", measures:   "$measures"     }     }
     }
   },
   {$project: { _id: 0, userId: "$_id.userId", actMes:1 } },
   {$sort: { userId:1}},     
 ]

)

This provides me 1 doc per user with under actmes the different activities+measures (but still double activities).

I've then tried to unwind measures:

  `db.armband.aggregate(
    [
     {$unwind: '$measures'},
     {$group: {
     _id: { userId: "$userId" },
    activity: { $addToSet: "$activity" },
    measures: {$addToSet: "$measures"}
         }
       },
     { $sort: {userId:1}}
    ])
  `

This gives me 1 document per user with 8 activities and measure around 5900 .

So I'm a bit lost how can I achieve what I would like? Is it possible, 1 document for 1 user, 1 activity, all the measures for that activity?

score 2 · Accepted Answer · answered Oct 29 '16 at 21:22

You can use :

1 $unwind to remove the array
1 $group that group by activity/userId and that $push measures into a new created array

The query is :

db.armband.aggregate([{
    "$unwind": "$measures"
}, {
    $group: {
        _id: {
            userId: "$userId",
            activity: "$activity"
        },
        measures: { $push: "$measures" }
    }
}])

This gives something like :

{ "_id" : { "userId" : 2, "activity" : "activity1" }, "measures" : [ { "M1" : 99, "M2" : 103, "M3" : 118, "M4" : 4 }, { "M1" : 136, "M2" : 89, "M3" : 108, "M4" : 6 } ] }
{ "_id" : { "userId" : 2, "activity" : "activity2" }, "measures" : [ { "M1" : 99, "M2" : 103, "M3" : 118, "M4" : 4 }, { "M1" : 136, "M2" : 89, "M3" : 108, "M4" : 6 } ] }
{ "_id" : { "userId" : 1, "activity" : "activity1" }, "measures" : [ { "M1" : 99, "M2" : 103, "M3" : 118, "M4" : 4 }, { "M1" : 136, "M2" : 89, "M3" : 108, "M4" : 6 }, { "M1" : 99, "M2" : 103, "M3" : 118, "M4" : 4 }, { "M1" : 136, "M2" : 89, "M3" : 108, "M4" : 6 } ] }

Thank you that's it. I first thought no I only get 50 documents per user for 1 activity when executing the code. But that's an issue in Robomongo. When I write it to a new collection ($out), I then get indeed all the documents, 1 per user, per activity with all the measures for that activity. Thanks again! — alve, Oct 30 '16 at 08:25

MongoDB aggregate: multiple group + elements array

1 Answers1