How to filter documents by week, hour and day using date field

user_15682493 0
Viewed 290

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?

mongoose express node.js mongodb
1 Answers

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
    }
]
user_107703700
Related