如何使用mongo聚合将两个或多个字段分组以查找区别

Question

我的预约文档看起来像这个

[{
"_id" : ObjectId("5f25686c946376355468caab"),
"status" : "approved",
"slot" : ObjectId("5ee751ab85596308c0272fa2"),
"student" : ObjectId("5eddc7d7cc5d3608c0393ce1"),
"teacher" : ObjectId("5eccfd6d4f5d8d48ac567a5d"),
"cost" : 49,
"createdAt" : ISODate("2020-08-01T13:04:44.696Z"),
"updatedAt" : ISODate("2020-08-01T13:20:36.164Z"),
"decisionTime" : ISODate("2020-08-01T13:20:36.161Z")
},
{
"_id" : ObjectId("5f25687b946376355468caac"),
"status" : "approved",
"slot" : ObjectId("5ee751ab85596308c0272fa3"),
"student" : ObjectId("5eddc7d7cc5d3608c0393ce1"),
"teacher" : ObjectId("5eccfd6d4f5d8d48ac567a5d"),
"cost" : 49,
"createdAt" : ISODate("2020-08-01T13:04:59.125Z"),
"updatedAt" : ISODate("2020-08-01T13:06:12.289Z"),
"decisionTime" : ISODate("2020-08-01T13:06:12.288Z")
},
{
"_id" : ObjectId("5f2ad883f0971a0c3c7d6e6f"),
"status" : "approved",
"slot" : ObjectId("5ee751ab85596308c0272fa4"),
"student" : ObjectId("5eddc7f4cc5d3608c0393ce3"),
"teacher" : ObjectId("5eccfd6d4f5d8d48ac567a5d"),
"cost" : 49,
"createdAt" : ISODate("2020-08-05T16:04:19.437Z"),
"updatedAt" : ISODate("2020-08-05T16:04:52.616Z"),
"decisionTime" : ISODate("2020-08-05T16:04:52.615Z")
}]

我想使用mongo聚合对不同学生的总数、预约的总数、特定日期的总成本(createdAt(进行分组。如何在不同的日期获得不同的学生

预期输出：

[
{
"_id": "01-08-2020",
"appointments": 2,
"totalCost": 98,
"totalStudents": 1
},
{
"_id": "05-08-2020",
"appointments": 1,
"totalCost": 49,
"totalStudents": 1
}
]

这里的问题是，我想找到不同学生的总数

Answer 1

Group bycreatedAt字段的日、月、年，使用$dateFromParts运算符，只需将cost字段相加即可。

为了获得不同的学生字段，在分组时使用$addToSet运算符并将其推送到一个集合，在项目阶段只投影该集合的size。

还要根据您的要求%d-%m-%Y格式化createdAt字段$dateToString运算符。

db.collection.aggregate([
{
$group: {
_id: {
$dateFromParts: {
day: {
$dayOfMonth: '$createdAt'
},
month: {
$month: '$createdAt'
},
year: {
$year: '$createdAt'
}
}
},
createdAt: {
$first: '$createdAt'
},
totalAppointments: {
$sum: 1
},
totalCost: {
$sum: '$cost'
},
students: {
$addToSet: '$student'
}
}
},
{
$project: {
_id: {
$dateToString: {
date: '$createdAt',
format: '%d-%m-%Y'
}
},
appointments: '$totalAppointments',
totalCost: '$totalCost',
totalStudents: {
$size: '$students'
}
}
}
]);

给出输出：

[
{
"_id": "05-08-2020",
"appointments": 1,
"totalCost": 49,
"totalStudents": 1
},
{
"_id": "01-08-2020",
"appointments": 2,
"totalCost": 98,
"totalStudents": 1
}
]

MongoDb playground