What is the best way to find unique visitors based on unique visitorId in this data? I'm using Mongoose in a Nodejs/Express environment.
{
username: "jack",
events: [
{
eventType: "party",
createdAt: "2022-01-23T10:26:11.214Z",
visitorInfo: {
visitorId: "87654321-0ebb-4238-8bf7-87654321"
}
},
{
eventType: "party",
createdAt: "2022-01-30T10:26:11.214Z",
visitorInfo: {
visitorId: "87654321-0ebb-4238-8bf7-87654321"
}
},
{
eventType: "party",
createdAt: "2022-01-29T10:26:11.214Z",
visitorInfo: {
visitorId: "12345678-0ebb-4238-8bf7-12345678"
}
},
{
eventType: "party",
createdAt: "2022-01-31T10:16:11.214Z",
visitorInfo: {
visitorId: "12345678-0ebb-4238-8bf7-12345678"
}
}
]
}
What I'm trying:
Event.aggregate([
{ $match: "jack" },
{ $unwind: "$events" },
{ $match: { "events.eventType": "party" } },
{
$group: {
_id: "$events.visitorInfo.visitorId",
count: { $sum: 1 },
},
},
]);
This gives frequency of unique visitorIds, and I think there could be a way to use that to get unique visitors, but I'm trying to find a faster and quicker way to find number of unique visitors based on unique visitorId before going down the rabbit hole.
what I'm expecting in the end:
visitors: {
totalVisitors: 4,
uniqueVisitors: 2
}
Appreciate any help or guidance. Thanks!
I think you are actually on the right track. You just need to do a $reduce to keep the totalVisitors before $unwind. And at the final stage, do another $group to get your expected result.
db.event.aggregate([
{
"$match": {
username: "jack"
}
},
{
"$addFields": {
"totalVisitors": {
"$reduce": {
"input": "$events",
"initialValue": 0,
"in": {
"$cond": {
"if": {
$eq: [
"$$this.eventType",
"party"
]
},
"then": {
$add: [
"$$value",
1
]
},
"else": "$$value"
}
}
}
}
}
},
{
$unwind: "$events"
},
{
$match: {
"events.eventType": "party"
}
},
{
$group: {
_id: "$events.visitorInfo.visitorId",
count: {
$sum: 1
},
totalVisitors: {
$first: "$totalVisitors"
}
}
},
{
$group: {
_id: null,
count: {
$first: "$count"
},
totalVisitors: {
$first: "$totalVisitors"
}
}
}
])
Here is the Mongo Playground for your reference.
version 1(filter):
IMHO this seems to be the best version , for best results you will need index on "username"
db.collection.aggregate([
{
$match: {
username: "jack"
}
},
{
$project: {
p: {
$filter: {
input: "$events",
as: "item",
cond: {
$eq: [
"$$item.eventType",
"party"
]
}
}
}
}
},
{
$unwind: "$p"
},
{
$group: {
_id: "0",
visitors: {
$push: "$p.visitorInfo.visitorId"
},
uniqueVisitors: {
$addToSet: "$p.visitorInfo.visitorId"
}
}
},
{
$project: {
_id: 0,
visitorsCount: {
$size: "$visitors"
},
uniqueVisitorsCount: {
$size: "$uniqueVisitors"
}
}
}
])
Explained:
version 2 ( not using filter - suitable for the atlas free tire)
db.collection.aggregate([
{
$match: {
username: "jack"
}
},
{
$unwind: "$events"
},
{
$match: {
"events.eventType": "party"
}
},
{
$group: {
_id: "0",
visitors: {
$push: "$events.visitorInfo.visitorId"
},
uniqueVisitors: {
$addToSet: "$events.visitorInfo.visitorId"
}
}
},
{
$project: {
_id: 0,
visitorsCount: {
$size: "$visitors"
},
uniqueVisitorsCount: {
$size: "$uniqueVisitors"
}
}
}
])
play ( version without filter suitable for the atlas free tire )