I need to assign a default value of zero on days with zero repair, but this is the result.
[
{ day: 21, month: 10, year: 2022, count: 2 },
{ day: 28, month: 10, year: 2022, count: 1 },
{ day: 24, month: 10, year: 2022, count: 2 }
]
I just need to access the weekly repair data, 0 should be the default on non-repair days
const result = await Repair.aggregate([
{
$match: {
createdDate: {
$gte: new Date(fromDate),
$lte: new Date(toDate),
},
},
},
{
$group: {
_id: {
day: "$day",
year: "$year",
month: "$month",
},
count: {
$sum: 1,
},
},
},
{
$project: {
_id: 0,
day: "$_id.day",
month: "$_id.month",
year: "$_id.year",
count: "$count",
},
},
]);
CodePudding user response：
Without valid sample input data, it is difficult to give exact solution, but would be like this one:
db.collection.aggregate([
{
$match: {
createdDate: {
$gte: new Date(fromDate),
$lte: new Date(toDate),
},
},
},
{
$group: {
_id: { $dateTrunc: { date: "$createdDate", unit: "day" } },
count: { $sum: 1 },
},
},
{ $set: { createdDate: "$_id" } },
{
$densify: {
field: "createdDate",
range: {
step: 1,
unit: "day",
bounds: "full"
}
}
},
{
$fill: {
sortBy: { createdDate: 1 },
output: { count: { value: 0 } }
}
}
]);
Update
With MongoDB version 5 the code is a bit more complex. Would be this one:
db.collection.aggregate([
{
$match: {
createdDate: {
$gt: new Date("2022-10-23T00:00:00.000Z"),
$lt: new Date("2022-10-30T00:00:00.000Z")
}
}
},
{
$facet: {
repairs: [
{
$group: {
_id: { $dateTrunc: { date: "$createdDate", unit: "day" } },
count: { $count: {} }
}
},
{
$project: {
date: "$_id",
count: "$count",
_id: 0
}
}
]
}
},
{
$set: {
allDays: {
$range: [
0,
{
$add: [
{
$dateDiff: {
startDate: { $min: "$repairs.date" },
endDate: { $max: "$repairs.date" },
/*
or
startDate: new Date("2022-10-23T00:00:00.000Z"),
endDate: new Date("2022-10-30T00:00:00.000Z"),
*/
unit: "day",
}
},
1
]
}
]
}
}
},
{
$set: {
allDays: {
$map: {
input: "$allDays",
in: {
$dateAdd: {
startDate: { $min: "$repairs.date" },
unit: "day",
amount: "$$this"
}
}
}
}
}
},
{
$project: {
repairs: {
$map: {
input: "$allDays",
as: "day",
in: {
$mergeObjects: [
{ date: "$$day", count: 0 },
{
$first: {
$filter: {
input: "$repairs",
cond: {
$eq: [
"$$day",
"$$repairs.date"
]
},
as: "repairs"
}
}
}
]
}
}
}
}
},
{
$project: {
repairs: {
$map: {
input: "$repairs",
in: "$$this.count"
}
}
}
}
])
The result cannot be simple [ 2, 0, 0, 0, 1, 2 ], the result is always a JSON document, i.e. field and values. But you can do
db.collection.aggregate([...]).toArray().shift().repairs