I have the following data schema and trying to find frequency of 'eventType' based on 'country' field. I'm using Mongoose in a Nodejs app.
{
username: "jack",
events: [
{
eventType: "party",
createdAt: "2022-01-23T10:26:11.214Z",
visitorInfo: {
location: {
country: 'US'
}
}
},
{
eventType: "party",
createdAt: "2022-01-30T10:26:11.214Z",
visitorInfo: {
location: {
country: 'US'
}
}
},
{
eventType: "party",
createdAt: "2022-01-29T10:26:11.214Z",
visitorInfo: {
location: {
country: 'US'
}
}
},
{
eventType: "meeting",
createdAt: "2022-01-29T10:26:11.214Z",
visitorInfo: {
location: {
country: 'US'
}
}
},
{
eventType: "meeting",
createdAt: "2022-01-31T10:16:11.214Z",
visitorInfo: {
location: {
country: 'GB'
}
}
},
{
eventType: "meeting",
createdAt: "2022-01-31T10:16:11.214Z",
visitorInfo: {
location: {
country: 'GB'
}
}
},
{
eventType: "party",
createdAt: "2022-01-31T10:16:10.214Z",
visitorInfo: {
location: {
country: 'GB'
}
}
}
]
}
What I'm trying so far:
db.collection.aggregate([
{
$match: {
username: "jack"
}
},
{
$unwind: "$events"
},
{
$match: {
"events.eventType": "party"
}
},
countries: [
{
$group: {
_id: "$events.visitorInfo.location.country",
frequency: { $sum: 1 },
},
}
{
$project: {
_id: 0,
countries: "$countries"
}
}
]
}
])
I'm trying to match on event types here and then group on country. Another approach could be to first get a list of unique countries then find event frequency on each of those countries. Any feedback is appreciated on these approaches.
Basically what I'm hoping to get as result:
countryAndEventFrequency: [
{
country: 'US',
party: 3,
meeting: 1
},
{
country: 'GB',
party: 2,
meeting: 1
},
]
Appreciate any help or guidance. Thanks!
db.collection.aggregate([
{
$unwind: "$events"
},
{
$project: {
party: {
$cond: [
{
$eq: [
"$events.eventType",
"party"
]
},
1,
0
]
},
meeting: {
$cond: [
{
$eq: [
"$events.eventType",
"meeting"
]
},
1,
0
]
},
country: "$events.visitorInfo.location.country"
}
},
{
$group: {
_id: "$country",
meeting: {
$sum: "$meeting"
},
party: {
$sum: "$party"
}
}
},
{
$project: {
country: "$_id",
meeting: 1,
party: 1
}
},
{
$group: {
_id: "cc",
countryAndEventFrequency: {
$push: "$$ROOT"
}
}
},
{
$project: {
_id: 0,
countryAndEventFrequency: 1
}
}
])
explained: