I have a Mongo database filled with "Events" records, that look like this:
{
timestamp: 2022-03-15T22:11:34.711Z,
_id: new ObjectId("62310f16b0d71321e887a905")
}
Using a NodeJs server, I need to fetch the last 30 days of Events, grouped/summed by date, and any dates within that 30 days with no records need to be filled with 0.
Using this code I can get the correct events, grouped/summed by date:
Event.aggregate( [
{
$match: {
timestamp: {
$gte: start,
$lte: end,
}
}
},
{
$project: {
date: {
$dateToParts: { date: "$timestamp" }
},
}
},
{
$group: {
_id: {
date: {
year: "$date.year",
month: "$date.month",
day: "$date.day"
}
},
"count": { "$sum": 1 }
}
}
] )
This will return something like this:
[
{
"_id": {
"date": {
"year": 2022,
"month": 3,
"day": 14
}
},
"count": 3
},
{
"_id": {
"date": {
"year": 2022,
"month": 3,
"day": 15
}
},
"count": 8
},
]
I also have this Javascript code to generate the last 30 days of dates:
const getDateRange = (start, end) => {
const arr = [];
for(let dt = new Date(start); dt <= end; dt.setDate(dt.getDate() 1)){
arr.push(new Date(dt));
}
return arr;
};
const subtractDays = (date, days) => {
return new Date(date.getTime() - (days * 24 * 60 * 60 * 1000));
}
const end = new Date();
const start = subtractDays(end, 30);
const range = getDateRange(start, end);
Which returns something like this:
[
2022-03-09T01:13:10.769Z,
2022-03-10T01:13:10.769Z,
2022-03-11T01:13:10.769Z,
2022-03-12T01:13:10.769Z,
2022-03-13T01:13:10.769Z,
...
]
It seems like I have all the pieces, but I'm having trouble putting all this together to do what I need in an efficient way. Any push in the right direction would be appreciated.
CodePudding user response：
Whenever one has to work with date/time arithmetic then I recommend a library like moment.js
const end = moment().startOf('day').toDate();
const start = moment().startOf('day').subtract(30, 'day').toDate();
In MongoDB version 5.0 you can use $dateTrunc(), which is shorter than $dateToParts and { year: "$date.year", month: "$date.month", day: "$date.day" }
You need to put all data in an array ({$group: {_id: null, data: { $push: "$$ROOT" }}) and then at missing elements with $ifNull:
event.aggregate([
{
$match: {
timestamp: { $gte: start, $lte: end }
}
},
{
$group: {
_id: { $dateTrunc: { date: "$timestamp", unit: "day" } },
count: { $sum: 1 }
}
},
{ $project: {timestamp: "$_id", count: 1, _id: 0} },
{
$group: {
_id: null,
data: { $push: "$$ROOT" }
}
},
{
$set: {
data: {
$map: {
input: { $range: [0, 30] },
as: "i",
in: {
$let: {
vars: {
day: { $dateAdd: { startDate: start, unit: "$$i", amount: "day" } }
},
in: {
$ifNull: [
{
$first: {
$filter: {
input: "$data",
cond: { $eq: ["$$this.timestamp", "$$day"] }
}
}
},
{ timestamp: "$$day", count: 0 }
]
}
}
}
}
}
}
},
{ $unwind: "$data" }
])
$range operator supports only integer values, that's the reason for using $let. Otherwise, if you prefer to use the external generated range, it would be
{
$set: {
data: {
$map: {
input: range,
as: "day",
in: {
$ifNull: [
{
$first: {
$filter: {
input: "$data",
cond: { $eq: ["$$this.timestamp", "$$day"] }
}
}
},
{ timestamp: "$$day", count: 0 }
]
}
}
}
}
}
And for MongoDB version 5.1 you may have a look at $densify
CodePudding user response：
Use aggregation stage densify if you're using MongoDB version 5.1 or later. But for lower version, below query can be used.
db.collection.aggregate([
{
$match: {
timestamp: {
$gte: {
"$date": "2022-03-01T00:00:00.000Z"
},
$lte: {
"$date": "2022-03-31T23:59:59.999Z"
},
}
}
},
{
$project: {
date: {
$dateToParts: {
date: "$timestamp"
}
},
}
},
{
$group: {
_id: {
date: {
year: "$date.year",
month: "$date.month",
day: "$date.day"
}
},
"count": {
"$sum": 1
}
}
},
{
"$group": {
"_id": null,
"originData": {
"$push": "$$ROOT"
}
}
},
{
"$project": {
"_id": 0,
"data": {
"$concatArrays": [
{
"$map": {
"input": {
"$range": [
0,
30,
1
]
},
"in": {
"$let": {
"vars": {
"date": {
"$add": [
{
"$date": "2022-03-01T00:00:00.000Z"
},
{
"$multiply": [
"$$this",
86400000
]
}
]
}
},
"in": {
"_id": {
"date": {
"day": {
"$dayOfMonth": "$$date"
},
"month": {
"$month": "$$date"
},
"year": {
"$year": "$$date"
}
}
},
"count": 0
}
}
}
}
},
"$originData"
]
}
}
},
{
"$unwind": "$data"
},
{
$group: {
_id: {
date: {
year: "$data._id.date.year",
month: "$data._id.date.month",
day: "$data._id.date.day"
}
},
"count": {
"$sum": "$data.count"
}
}
},
{
"$sort": {
"_id.date.year": 1,
"_id.date.month": 1,
"_id.date.day": 1
}
}
])
Link to online playground. https://mongoplayground.net/p/5I0I04HoHXm