I am a beginner in express and mongoDB. I was working on a task where I have a model called sales of the type -
{
userName : String,
amount : Number,
date : Date
}
Now I have to create an API which should have a param either of 3 types - "daily", "weekly", "monthly". If the param is
daily - then I have to send stats(sum of amount) on the basis of each hour of the day from sales table.
weekly - then I have to send stats on basis of each day of the week
monthly - then I have to send stats on the basis of each day of the month
After thinking of some logic I came up with this -
I was able to do the daily stats but it also looks complex
app.get("/fetchSales/:id",async (req, res)=>{
const { id } = req.params;
const allSales = await sales.find({});
const dates = [];
for(i in allSales){
let date = allSales[i].date.toISOString().replace('Z', '').replace('T', '');
const dateFormat = dayjs(date);
dateFormat.amount = allSales[i].amount;
dates.push(dateFormat);
}
if(id === "daily") {
const stats = {};
for(let hour = 0; hour < 24; hour++) {
let sum = 0
for (x in dates) {
if (dates[x]["$H"] === hour) sum += dates[x].amount
}
stats[hour] = sum;
}
res.send(stats);
}
})
But going forward I have to use several loop and it's not looking good. I have read that maybe aggregate queries could help me.
structure days js gives me is -
d {
'$L': 'en',
'$d': 2021-01-13T13:00:00.000Z,
'$x': {},
'$y': 2021,
'$M': 0,
'$D': 13,
'$W': 3,
'$H': 18,
'$m': 30,
'$s': 0,
'$ms': 0,
amount: 1700 //added on my own//
}
Some output structure that I want - for daily
{
0: 0$,
1: 0$,
2: 24$,
...
23: 2$
}
Any idea how I can deal with this?
You can build your query condition based on input but let Mongodb do the heavy lifting:
app.get("/fetchSales/:id", async (req, res) => {
const {id} = req.params;
const groupCondition = {
sum: {$sum: 1}
};
if (id === 'monthly') {
groupCondition._id = {$week: '$date'};
} else if (id === 'weekly') {
groupCondition._id = {$dayOfMonth: '$date'};
} else if (id === 'weekly') {
groupCondition._id = {$hour: '$date'};
} else {
// can this happen? what to do here.
}
const allSales = await sales.aggregate([
{
$group: groupCondition
},
{
$sort: {
_id: 1
}
}
]);
});
Mind you this will not return days / weeks / hours with a document count of 0, you will have to manually insert them either through the pipeline or in code.
For actually $grouping per full date and not per a certain general hour / day you should use this:
app.get("/fetchSales/:id", async (req, res) => {
const {id} = req.params;
const groupCondition = {
_id: {year: {$year: '$date'}, $month: {'$month': "$date"}, week: {'$week': "$date"} },
sum: {$sum: 1},
date: {$first: '$date'} //for sorting
};
switch (id) {
case 'weekly':
groupCondition._id.$dayOfMonth = {$dayOfMonth: '$date'};
case 'daily':
groupCondition._id.$hour = {$hour: '$date'};
}
const allSales = await sales.aggregate([
{
$group: groupCondition
},
{
$sort: {
date: 1
}
},
{
$project: {
_id: 1,
sum: 1
}
}
]);
});
This will give you documents in this structure:
[
{
_id: { year: 2020, month: 5, week: 13, day: 22, hour: 5},
sum: 50
}
]