根据唯一id和日期查找总的唯一计数器-MongoDB

Question

我试图根据以下数据形状统计每天的所有和唯一事件：

{
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: "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"
}
}
]
}

我试着在日期(每天(统计事件(所有和唯一的事件都基于visitorId(。这就是我到目前为止所拥有的(感谢@R2D2的方法指南(：

Event.aggregate([
{ $match: { username: 'jack' } },
{ $unwind: "$events" },
{
$project: {
total: {
$cond: [
{
$eq: ["$events.eventType", "party"],
},
1,
0,
],
},
unique: { // where I'm stuck. I need to count unique events based on visitorId on current date.
$cond: [
{
$eq: ["$events.eventType", "party"],
},
1,
0,
],
},
date: "$events.createdAt",
},
},
{
$group: {
_id: {
$dateToString: { format: "%Y-%m-%d", date: "$date" },
},

total: {
$sum: "$total",
},
uniqueTotal: {
$sum: "$unique",
},
},
},
{
$project: {
date: "$_id",
total: 1,
uniqueTotal: 1,
},
},
{
$group: {
_id: "0",
dateAndEventFrequency: {
$push: "$$ROOT",
},
},
},
{
$project: {
_id: 0,
dateAndEventFrequency: 1,
},
},
]);

我尝试使用$addToSet，但它没有与$project一起使用(它与$group一起使用(。任何基于数据形状和我期望的结果的新方法都是受欢迎的。我使用$project是因为我已经在使用它了

基本上我希望最终得到的：

dateAndEventFrequency: [
{
_id: "2022-01-23",
uniqueTotal: 2,
total: 3,
date: "2022-01-23",
},
{
_id: "2022-01-30",
uniqueTotal: 1,
total: 2,
date: "2022-01-30",
},
]

感谢任何帮助或指导。谢谢

Answer 1

第一组由date和visitorId一起进行，然后仅由date进行另一组

你可以在mongo游乐场测试

db.collection.aggregate([
{
$match: {
username: "jack"
}
},
{
"$unwind": "$events"
},
{
"$group": {
"_id": {
date: {
"$dateToString": {
format: "%Y-%m-%d",
date: "$events.createdAt"
}
},
"visitorId": "$events.visitorInfo.visitorId",

},
"count": {
"$count": {}
}
}
},
{
"$group": {
"_id": "$_id.date",
"uniqueTotal": {
"$count": {}
},
total: {
"$sum": "$count"
}
}
}
])