Daily aggregation of user sessions - group by date, userId

[Andreas_West]

Hi everyone,

still struggling with aggregations and here especially with the $group stage.

I have the following data about user sessions with loginDate:

{ "_id" : "n2LXm3pzpbruqpPWm", "userId" : "ouFd8iwxDbxpLrNpQ", "loginDate" : ISODate("2021-04-07T18:12:04.867Z"), "logoutDate" : ISODate("2021-04-07T18:12:37.098Z"), "minutes" : 1 }
{ "_id" : "vSveSbd3bbHGeKCPv", "userId" : "ouFd8iwxDbxpLrNpQ", "loginDate" : ISODate("2021-04-07T18:12:04.867Z"), "logoutDate" : ISODate("2021-04-07T18:52:48.390Z"), "minutes" : 41 }
{ "_id" : "LNL4hpfWhZ7SFfqAC", "userId" : "ouFd8iwxDbxpLrNpQ", "loginDate" : ISODate("2021-04-08T15:00:47.425Z"), "logoutDate" : ISODate("2021-04-08T17:00:14.512Z"), "numCardsClicked" : 16, "numNewTG" : 7, "minutes" : 119 }
{ "_id" : "9hGcpdaso2DQYcvTk", "userId" : "ouFd8iwxDbxpLrNpQ", "loginDate" : ISODate("2021-04-08T17:04:53.973Z"), "logoutDate" : ISODate("2021-04-08T17:19:44.931Z"), "numCardsClicked" : 1, "minutes" : 15 }
{ "_id" : "HThgQYW28L2aQb8uu", "userId" : "ouFd8iwxDbxpLrNpQ", "loginDate" : ISODate("2021-04-08T17:04:53.973Z"), "logoutDate" : ISODate("2021-04-08T19:36:45.179Z"), "numCardsClicked" : 5, "minutes" : 152 }
{ "_id" : "7qvdsdsMhvkb2bHQh", "userId" : "ouFd8iwxDbxpLrNpQ", "loginDate" : ISODate("2021-04-08T21:25:10.617Z"), "logoutDate" : ISODate("2021-04-08T22:09:55.682Z"), "numCardsClicked" : 4, "minutes" : 45 }
{ "_id" : "4mXw2wGhwRjPYzJQB", "userId" : "ouFd8iwxDbxpLrNpQ", "loginDate" : ISODate("2021-04-09T00:15:29.073Z"), "logoutDate" : ISODate("2021-04-09T00:20:46.113Z"), "minutes" : 5 }
{ "_id" : "9eBXyjXXpwaSpeA2C", "userId" : "ouFd8iwxDbxpLrNpQ", "loginDate" : ISODate("2021-04-09T00:15:29.073Z"), "logoutDate" : ISODate("2021-04-09T03:48:07.185Z"), "numCardsClicked" : 4, "minutes" : 213 }

The above snapshot is for 1 specific user only.

I can get the sum of docs by date with the following aggregations:

$group: {
  _id: { $dateToString: { format: "%Y-%m-%d", date: "$loginDate"} },
  docs: {
    $sum: 1
  }
}

Based on the above data sample, I get 3 docs back:

_id: "2021-04-08"
docs: 4

_id: "2021-04-09"
docs: 2

_id: "2021-04-07"
docs: 2

This is how far I got with the help of SO and the MongoDb documentation.

However, I want to get achieve two more things:

1. A group by date, userId

How can I do this?

2. I don’t want to get the number of docs per day (which is what my current aggregation returns) but rather the sum of the individual fields in the docs:

sum(minutes)
sum(numCardsClicked)
sum(numNewTG)

How can I achieve that?

Thanks in advance!

[Prasad_Saya]

@Andreas_West, you can use the group stage with some modifications, as shown below:

$group: {
    _id: { 
        date: { $dateToString: { format: "%Y-%m-%d", date: "$loginDate"} },
        user: "$userId"
    },
    sum_minutes: { $sum: "$minutes" },
    sum_numCardsClicked: { ... },
    sum_numNewTG: { ... }
}

[Andreas_West]

Thanks so much, @Prasad_Saya. Once I entered your changes it all makes so much sense.