I have documents which contain a date and I'm wondering how to group them according to quarterly basis?
My schema is:
var ekgsanswermodel = new mongoose.Schema({
userId: {type: Schema.Types.ObjectId},
topicId : {type: Schema.Types.ObjectId},
ekgId : {type: Schema.Types.ObjectId},
answerSubmitted :{type: Number},
dateAttempted : { type: Date},
title : {type: String},
submissionSessionId : {type: String}
});
1st quarter contains months 1, 2, 3. 2nd quarter contains months 4, 5, 6 and so on up-to 4th quarter.
My final result should be:
"result" : [
{
_id: {
quater:
},
_id: {
quater:
},
_id: {
quater:
},
_id: {
quater:
}
}
Drag down the column to select the dates you want to sort. Click Home tab > arrow under Sort & Filter, and then click Sort Oldest to Newest, or Sort Newest to Oldest.
(1) In the Column drop down list, please specify the date column that you will sort by quarter. (2) Click the Sort On drop down list, and select Quarter. (3) Specify the sort order in the Order drop down list.
You could make use of the $cond
operator to check if:
$month
is <= 3
, project a field named quarter
with
value as "one".$month
is <= 6
, project a field named quarter
with
value as "two".$month
is <= 9
, project a field named quarter
with
value as "three".quarter
would be "fourth".$group
by the quarter
field.Code:
db.collection.aggregate([
{
$project: {
date: 1,
quarter: {
$cond: [
{ $lte: [{ $month: "$date" }, 3] },
"first",
{
$cond: [
{ $lte: [{ $month: "$date" }, 6] },
"second",
{
$cond: [{ $lte: [{ $month: "$date" }, 9] }, "third", "fourth"],
},
],
},
],
},
},
},
{ $group: { _id: { quarter: "$quarter" }, results: { $push: "$date" } } },
]);
Specific to your schema:
db.collection.aggregate([
{
$project: {
dateAttempted: 1,
userId: 1,
topicId: 1,
ekgId: 1,
title: 1,
quarter: {
$cond: [
{ $lte: [{ $month: "$dateAttempted" }, 3] },
"first",
{
$cond: [
{ $lte: [{ $month: "$dateAttempted" }, 6] },
"second",
{
$cond: [
{ $lte: [{ $month: "$dateAttempted" }, 9] },
"third",
"fourth",
],
},
],
},
],
},
},
},
{ $group: { _id: { quarter: "$quarter" }, results: { $push: "$$ROOT" } } },
]);
You could use following to group documents quarterly.
{
$project : {
dateAttempted : 1,
dateQuarter: {
$trunc : {$add: [{$divide: [{$subtract: [{$month:
"$dateAttempted"}, 1]}, 3]}, 1]}
}
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With