MongoDB group by ID and then by date

Question

I have a collection in my MongoDB database that stores durations for people who are in groups, it looks a like this:

[{
    "_id": "5c378eecd11e570240a9b0ac",
    "state": "DRAFT",
    "groupId": "5c378eebd11e570240a9ae49",
    "personId": "5c378eebd11e570240a9aee1",
    "date": "2019-01-07T00:00:00.000Z",
    "duration": 480,
    "__v": 0
},
{
    "_id": "5c378eecd11e570240a9b0bb",
    "state": "DRAFT",
    "groupId": "5c378eebd11e570240a9ae58",
    "personId": "5c378eebd11e570240a9aeac",
    "date": "2019-01-07T00:00:00.000Z",
    "duration": 480,
    "__v": 0
},
{
    "_id": "5c378eecd11e570240a9b0c5",
    "state": "DRAFT",
    "groupId": "5c378eebd11e570240a9ae3e",
    "personId": "5c378eebd11e570240a9aef6",
    "date": "2019-01-07T00:00:00.000Z",
    "duration": 480,
    "__v": 0
}]

I would like to be able to run an aggregate query which returns a collection of personIds and the duration grouped per day with the corresponding groupId, which would look like this:

[{
    "personId": "5c378eebd11e570240a9aee1",
    "time": [{
        "date": "2019-01-07T00:00:00.000Z",
        "entries": [{
            "groupId": "5c378eebd11e570240a9ae49",
            "duration": 480,
            "state": "DRAFT"
        }]
    }]
}, {
    "personId": "5c378eebd11e570240a9aeac",
    "time": [{
        "date": "2019-01-07T00:00:00.000Z",
        "entries": [{
            "groupId": "5c378eebd11e570240a9ae58",
            "duration": 480,
            "state": "DRAFT"
        }]
    }]
}, {
    "personId": "5c378eebd11e570240a9aef6",
    "time": [{
        "date": "2019-01-07T00:00:00.000Z",
        "entries": [{
            "groupId": "5c378eebd11e570240a9ae3e",
            "duration": 480,
            "state": "DRAFT"
        }]
    }]
}]

So far, I have written the following aggregation (I'm using Mongoose, hence the syntax):

Time.aggregate()
    .match({ date: { $gte: new Date(start), $lte: new Date(end) } })
    .group({
      _id: '$personId',
      time: { $push: { date: '$date', duration: '$duration', state: '$state' } },
    })
    .project({ _id: false, personId: '$_id', time: '$time' })

Which returns the following:

[{
    "personId": "5c378eebd11e570240a9aed1",
    "time": [{
        "date": "2019-01-11T00:00:00.000Z",
        "duration": 480,
        "state": "DRAFT"
    }, {
        "date": "2019-01-11T00:00:00.000Z",
        "duration": 480,
        "state": "DRAFT"
    }
    // ...
}]

Hopefully you can see that the durations are being grouped by personId but I've not been able to figure out how to apply another grouping to the time array as the dates are duplicated if a personId has more than one duration for a given date.

Is it possible to group by and ID, push to an array and then group the values in that array as an aggregation or will my application need to map/reduce the results instead?

Answer 1

I would suggest running two $group operations in a row:

db.time.aggregate({
  // first, group all entries by personId and date
  $group: {
    _id: {
      personId: "$personId",
      date: "$date"
    },
    entries: {
      $push: {
        groupId: "$groupId",
        duration: "$duration",
        state: "$state"
      }
    }
  }
}, {
  // then, group previously aggregated entries by personId
  $group: {
    _id: "$_id.personId",
    time: {
      $push: {
        date: "$_id.date",
        entries: "$entries"
      }
    }
  }
}, {
  // finally, rename _id to personId
  $project: {
    _id: 0,
    personId: "$_id",
    time: "$time"
  }
})

In Mongoose it should be something like that:

Time.aggregate()
  .match({
    date: {
      $gte: new Date(start),
      $lte: new Date(end)
    }
  })
  .group({
    _id: {
      personId: '$personId',
      date: '$date'
    },
    entries: {
      $push: {
        groupId: '$groupId',
        duration: '$duration',
        state: '$state'
      }
    }
  })
  .group({
    _id: '$_id.personId',
    time: {
      $push: {
        date: '$_id.date',
        entries: '$entries'
      }
    }
  })
  .project({
    _id: false,
    personId: '$_id',
    time: '$time'
  })

Answer 2

db.getCollection("dummyCollection").aggregate(
[
    { 
        "$group" : {
            "_id" : "$personId", 
            "time" : {
                "$push" : {
                    "date" : "$date", 
                    "duration" : "$duration", 
                    "state" : "$state"
                }
            }
        }
    }, 
    { 
        "$project" : {
            "_id" : false, 
            "personId" : "$_id", 
            "time" : "$time"
        }
    }, 
    { 
        "$unwind" : "$time"
    }, 
    { 
        "$group" : {
            "_id" : "$time.date", 
            "time" : {
                "$addToSet" : "$time"
            }
        }
    }
]

);

Use $addToSet which returns an array of all unique values that results from applying an expression to each document in a group of documents that share the same group by key.