2 / 2
Aug 2024

Hi,
I’m trying to use $setWindowFields to group my registered users by mktChannel AND on a daily basis. In addition, I need to count the total number of users registered daily.

Demo data:

[ { "_id": ObjectId("66b016d60e3592d71f966d1a"), "dateRegistered": ISODate("2024-08-01T00:03:34.334Z"), "name": "user1", "mktChannel": "facebook" }, { "_id": ObjectId("66b053710e3592d71f9672b5"), "dateRegistered": ISODate("2024-08-01T04:22:09.931Z"), "name": "user2", "mktChannel": "facebook" }, { "_id": ObjectId("66b066190e3592d71f9692cd"), "dateRegistered": ISODate("2024-08-01T05:41:45.110Z"), "name": "user3", "mktChannel": "linkedin" }, { "_id": ObjectId("66b070df0e3592d71f96a5d9"), "dateRegistered": ISODate("2024-08-02T06:27:43.740Z"), "name": "user4", "mktChannel": "instagram" }, { "_id": ObjectId("66b07a520e3592d71f96ba14"), "dateRegistered": ISODate("2024-08-02T07:08:02.896Z"), "name": "user5", "mktChannel": "facebook" }, { "_id": ObjectId("66b07c9f0e3592d71f96bb97"), "dateRegistered": ISODate("2024-08-02T07:17:51.406Z"), "name": "user6", "mktChannel": "facebook" }, { "_id": ObjectId("66b07d2c0e3592d71f96bcf0"), "dateRegistered": ISODate("2024-08-02T07:20:12.944Z"), "name": "user7", "mktChannel": "facebook" }, { "_id": ObjectId("66b081500e3592d71f96c557"), "dateRegistered": ISODate("2024-08-03T07:37:52.883Z"), "name": "user8", "mktChannel": "linkedin" }, { "_id": ObjectId("66b084940e3592d71f96ce96"), "dateRegistered": ISODate("2024-08-03T07:51:48.830Z"), "name": "user9", "mktChannel": "none" } ]

I want to return something like this:

[ { "day": "2024-08-01", "usersRegistered": 3, "usersByChannel": [ { "channel": "facebook", "count": 2, "usersIds": [ "66b016d60e3592d71f966d1a", "66b053710e3592d71f9672b5" ], "usersNames": [ "user1", "user2" ] }, { "channel": "linkedin", "count": 1, "users": [ "66b066190e3592d71f9692cd" ], "usersNames": [ "user3" ] } ] }, { "day": "2024-08-02", "usersRegistered": 4, "usersByChannel": [ { "channel": "instagram", "count": 2, "users": [ "66b070df0e3592d71f96a5d9", "66b07c9f0e3592d71f96bb97" ], "usersNames": [ "user4", "user6" ] }, { "channel": "email", "count": 1, "users": [ "66b07a520e3592d71f96ba14" ], "usersNames": [ "user5" ] } ] }, { "day": "2024-08-03", "usersRegistered": 2, "usersByChannel": [ { "channel": "linkedin", "count": 1, "users": [ "66b081500e3592d71f96c557" ], "usersNames": [ "user8" ] }, { "channel": "none", "count": 1, "users": [ "66b084940e3592d71f96ce96" ], "usersNames": [ "user9" ] } ] } ]

I knew (more or less) how to achive this result by using multiple $group stages, but I really can’t figure out how to solve this using the (relative) new stage $setWindowFields . Tried using range unit “day” but not obtaing anything useful.

Can someone help me or just show the direction?

DEMO CODE PLAYGROUND HERE: Mongo playground

@Luca_Mazzill , Your expected output does not pertain structure of your original document . $setWindowFields is used compare one document with other , carrying the result of window operator ( think of it as reduce function in JS, result is used in next iteration ) . IMHO $setWindowFields would be a good stage to do this , better stick to $group .