Calculating day-of-week frequency with Mongo Aggregate

user_493510 0
Viewed 49

I'm trying to calculate the frequency of site visits using a Mongo Aggregate function, ie. in a given week, how many days did a user visit the site?

{ "_id" : ObjectId("5f7720caf2b93af8d566bc7c"), "email" : "blah@gmail.com", "timestamp" : ISODate("2020-09-29T17:59:00Z") }
{ "_id" : ObjectId("5f7720dcf2b93af8d566ffb7"), "email" : "blah@gmail.com", "timestamp" : ISODate("2020-09-30T01:01:00Z") }
{ "_id" : ObjectId("5f7721bbf2b93af8d56aadc4"), "email" : "yack@gmail.com", "timestamp" : ISODate("2020-10-01T09:58:00Z") }
{ "_id" : ObjectId("5f771e9ff2b93af8d55c57a9"), "email" : "yack@gmail.com", "timestamp" : ISODate("2020-09-26T04:12:00Z") }
{ "_id" : ObjectId("5f771e9ff2b93af8d55c5f6b"), "email" : "yack@gmail.com", "timestamp" : ISODate("2020-09-26T04:22:00Z") }
{ "_id" : ObjectId("5f771eeaf2b93af8d55dc45c"), "email" : "yack@gmail.com", "timestamp" : ISODate("2020-09-27T04:11:00Z") }

Output I'd like:

[
   { "_id": "blah@gmail.com", "dow" [ 2, 3 ], "visits": 2 }, // Visited Tuesday and Wednesday
   { "_id": "yack@gmail.com", "dow" [ 0, 1, 2, 3, 4, 5 ], "visits": 6 } // Visited Sunday through to Friday
]

I can get each email/dow pair as a record, but I'm not sure where to go from here...

[
    {
        $group: {
            _id: { email: "$email", dow: { $dayOfWeek: "$timestamp" } },
        }
    }
]

Outputs:

{ "_id" : { "email" : "blah@gmail.com", "dow" : 2 } }
{ "_id" : { "email" : "blah@gmail.com", "dow" : 3 } }
{ "_id" : { "email" : "yack@gmail.com", "dow" : 0 } }
{ "_id" : { "email" : "yack@gmail.com", "dow" : 1 } }
{ "_id" : { "email" : "yack@gmail.com", "dow" : 2 } }
{ "_id" : { "email" : "yack@gmail.com", "dow" : 3 } }
{ "_id" : { "email" : "yack@gmail.com", "dow" : 4 } }
{ "_id" : { "email" : "yack@gmail.com", "dow" : 5 } }

Thanks!

aggregation-framework mongodb
1 Answers

You can use another $group statement:

db.collection.aggregate([
    {
        $group: {
            _id: { email: "$email", dow: { $dayOfWeek: "$timestamp" } }
        }
    },
    {
        $group: {
            _id: "$_id.email",
            dow: { $push: "$_id.dow" },
            visits: { $sum: 1 }
        }
    }
])

Mongo Playground

user_62389770
Related