How to group date quarterly wise in MongoDB
You could make use of the $cond
operator to check if:
- The
$month
is<= 3
, project a field namedquarter
with value as "one". - The
$month
is<= 6
, project a field namedquarter
with value as "two". - The
$month
is<= 9
, project a field namedquarter
with value as "three". - else the value of the field
quarter
would be "fourth". - Then
$group
by thequarter
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]}
}
}
}