'Aggregate total and unique counts based on value type and unique visitorId - MongoDB
Similar to another question I had (Here). But now I'm trying to count unique and total events on daily basis for each event type, based on the following data shape:
{
username: "jack",
events: [
{
eventType: "party",
createdAt: "2022-01-23T10:26:11.214Z",
visitorInfo: {
visitorId: "87654321-0ebb-4238-8bf7-87654321"
}
},
{
eventType: "party",
createdAt: "2022-01-23T10:26:11.214Z",
visitorInfo: {
visitorId: "87654321-0ebb-4238-8bf7-87654321"
}
},
{
eventType: "party",
createdAt: "2022-01-23T10:26:11.214Z",
visitorInfo: {
visitorId: "01234567-0ebb-4238-8bf7-01234567"
}
},
{
eventType: "meeting",
createdAt: "2022-01-23T12:26:11.214Z",
visitorInfo: {
visitorId: "87654321-0ebb-4238-8bf7-87654321"
}
},
{
eventType: "meeting",
createdAt: "2022-01-23T11:26:11.214Z",
visitorInfo: {
visitorId: "87654321-0ebb-4238-8bf7-87654321"
}
},
{
eventType: "meeting",
createdAt: "2022-01-23T12:26:11.214Z",
visitorInfo: {
visitorId: "01234567-0ebb-4238-8bf7-01234567"
}
},
{
eventType: "party",
createdAt: "2022-01-30T10:26:11.214Z",
visitorInfo: {
visitorId: "12345678-0ebb-4238-8bf7-12345678"
}
},
{
eventType: "party",
createdAt: "2022-01-30T10:16:11.214Z",
visitorInfo: {
visitorId: "12345678-0ebb-4238-8bf7-12345678"
}
},
{
eventType: "meeting",
createdAt: "2022-01-30T12:36:11.224Z",
visitorInfo: {
visitorId: "12345678-0ebb-4238-8bf7-12345678"
}
},
{
eventType: "meeting",
createdAt: "2022-01-30T11:46:11.314Z",
visitorInfo: {
visitorId: "12345678-0ebb-4238-8bf7-12345678"
}
}
]
}
I'm trying to count events (all and unique ones based on visitorId) on date (daily). This is what I have so far (thanks to @R2D2's guide on the approach):
Event.aggregate([
{ $match: { username: 'jack' } },
{ $unwind: "$events" },
{
$project: {
totalPartyEvents: {
$cond: [
{
$eq: ["$events.eventType", "party"],
},
1,
0,
],
},
uniquePartyEvents: { // where I'm stuck. I need to count unique events based on visitorId on current date for 'party' event type.
$cond: [
{
$eq: ["$events.eventType", "party"],
},
1,
0,
],
},
totalMeetingEvents: {
$cond: [
{
$eq: ["$events.eventType", "meeting"],
},
1,
0,
],
},
uniqueMeetingEvents: { // do the same for other events. maybe there's a better way to combine these (with facets).
$cond: [
{
$eq: ["$events.eventType", "meeting"],
},
1,
0,
],
},
date: "$events.createdAt",
},
},
{
$group: {
_id: {
$dateToString: { format: "%Y-%m-%d", date: "$date" },
},
totalPartyEvents: {
$sum: "$totalMeetingEvents",
},
uniquePartyEvents: {
$sum: "$totalMeetingEvents",
},
totalMeetingEvents: {
$sum: "$totalMeetingEvents",
},
uniqueMeetingEvents: {
$sum: "$uniqueMeetingEvents",
},
},
},
{
$project: {
date: "$_id",
uniquePartyEvents: 1,
totalPartyEvents: 1,
totalMeetingEvents:1,
uniqueMeetingEvents: 1,
},
},
{
$group: {
_id: "0",
dateAndEventFrequency: {
$push: "$$ROOT",
},
},
},
{
$project: {
_id: 0,
dateAndEventFrequency: 1,
},
},
]);
I tried using $addToSet but it's not used with $project (it works with $group).
Any new approach is welcome based on the data shape and the desired result I'm expecting. I used $project because I was already using it.
Basically what I'm hoping to get in the end:
dateAndEventFrequency: [
{
_id: "2022-01-23",
totalPartyEvents: 3,
uniquePartyEvents: 2,
totalMeetingEvents: 3,
uniqueMeetingEvents: 2,
date: "2022-01-23",
},
{
_id: "2022-01-30",
totalPartyEvents: 2,
uniquePartyEvents: 1,
totalMeetingEvents: 2,
uniqueMeetingEvents: 1,
date: "2022-01-30",
},
]
I'm using Mongoose and Nodejs. Any help or guidance is appreciated. Thanks!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
