Preserve $group collapsed fields
this is not me
I want to summarize a set of documents by relying on a field called code. How can I aggregate the data and retain the details in the original document?
The pipeline input contains the following documents.
{
"_id" : ObjectId("5ff38e0eb09dec2cbce14760"),
"code" : "U",
"date" : ISODate("2021-04-09T00:00:00.000+0000"),
"full_day" : false,
"remote" : false,
"student_id" : 9441
}
{
"_id" : ObjectId("5ff38e0eb09dec2cbce14807"),
"code" : "E",
"date" : ISODate("2020-11-02T00:00:00.000+0000"),
"full_day" : false,
"remote" : false,
"student_id" : 9441
}
{
"_id" : ObjectId("5ff39854b09dec2cbce1494c"),
"code" : "E",
"date" : ISODate("2020-11-03T08:00:00.000+0000"),
"full_day" : true,
"remote" : false,
"student_id" : 9441
}
The desired output is grouped by code, promoted student_idto the root level, and nested with additional details in detailsan array:
{
"code" : "U",
"student_id": 9441,
"count" : 1.0,
"details" : [
{
"date" : ISODate("2021-04-09T00:00:00.000+0000"),
"full_day" : false,
"remote" : false,
}
]
}
{
"code" : "E",
"student_id": 9441,
"count" : 2.0,
"details" : [
{
"date" : ISODate("2020-11-02T00:00:00.000+0000"),
"full_day" : false,
"remote" : false,
},
{
"date" : ISODate("2020-11-03T08:00:00.000+0000"),
"full_day" : true,
"remote" : false,
}
]
}
Combined $group, $pushI can only produce:
{
"_id" : "U",
"count" : 1.0,
"details" : [
{
"date" : ISODate("2021-04-09T00:00:00.000+0000"),
"full_day" : false,
"remote" : false,
"student_id" : 9441
}
]
}
{
"_id" : "E",
"count" : 2.0,
"details" : [
{
"date" : ISODate("2020-11-02T00:00:00.000+0000"),
"full_day" : false,
"remote" : false,
"student_id" : 9441
},
{
"date" : ISODate("2020-11-03T08:00:00.000+0000"),
"full_day" : true,
"remote" : false,
"student_id" : 9441.0
}
]
}
The above result is achieved with the following pipeline:
[
{
"$match" : {
"student_id" : 9441.0
}
},
{
"$group" : {
"_id" : "$code",
"count" : {
"$sum" : 1.0
},
"details" : {
"$push" : {
"date" : "$date",
"full_day" : "$full_day",
"remote" : "$remote",
"student_id" : "$student_id"
}
}
}
},
{
"$addFields" : {
"student_id" : "$student_id"
}
}
]
Joe
If you want a field to have the same value for all input documents, and you want that field to be included in the $groupoutput, use the $first accumulation operator:
{
"$group" : {
"_id" : "$code",
"student_id" : {$first: "$student_id"},
"count" : {
"$sum" : 1.0
},
"details" : {
"$push" : {
"date" : "$date",
"full_day" : "$full_day",
"remote" : "$remote"
}
}
}
}
If you need to rename _idto code, use a stage $projectafter the group .