Counting total records for each day in a given week

Working with Data
,
1

I’m working on a dashboard analytics region that should be able to show the the number of user registrations for different types of users (roles) for a given time period such as “This Week”, “Last 30 days” or “this Year”. I’m working on the first one and I’ve been able to do some filtering with the following progress:

let pipeline = [];

        // Default filter type
        params.filter = '7days';
        // params.filter = params.filter || '7days';

        // Construct the aggregation pipeline
        // let currentWeek = getWeek(new Date());
        let currentWeek = 50;
        // let currentYear = new Date().getFullYear();


        // Set the time period based on the createdAt field
        pipeline.push({
            $project: {
                year: {
                    $year: "$createdAt",
                },
                month: {
                    $month: "$createdAt",
                },
                week: {
                    $week: "$createdAt",
                },
                day: {
                    $dayOfWeek: "$createdAt",
                },
            },
        });

        if (params.filter === '7days') {
            pipeline.push({
                $match: {
                    // year: currentYear,
                    week: currentWeek,
                },
            });
        }

        pipeline.push({
            $group: {
                _id: "$day",
                count: { $sum: 1 },
            },
        },
            {
                $project: {
                    _id: 0,
                    day: {
                        $switch: {
                            branches: [
                                {
                                    case: { $eq: ["$_id", 1] },
                                    then: "Sunday",
                                },
                                {
                                    case: { $eq: ["$_id", 2] },
                                    then: "Monday",
                                },
                                {
                                    case: { $eq: ["$_id", 3] },
                                    then: "Tuesday",
                                },
                                {
                                    case: { $eq: ["$_id", 4] },
                                    then: "Wednesday",
                                },
                                {
                                    case: { $eq: ["$_id", 5] },
                                    then: "Thursday",
                                },
                                {
                                    case: { $eq: ["$_id", 6] },
                                    then: "Friday",
                                },
                                {
                                    case: { $eq: ["$_id", 7] },
                                    then: "Saturday",
                                },
                            ],
                            default: "Unknown",
                        },
                    },
                    count: 1,
                },
            });

Which gives an output like the following:

[{day:"Wednesday", count: 3}, {day:"Friday", count: 7}]

BUt i need to be able to set zero values by default for every other day if there is nothing to count. So for e.g. Monday will be 0 and so on. That way I will have all 7 days with a count to send to a frontend client to show a UI chart.

How can I do so?

I also plan to use this for months of the year and will figure out how to do it for everyday in the month later.

2

Hello @Awakening_Quasar, Welcome back to the MongoDB community forum,

Well, I don’t recommend doing all the logic in the aggregation query, also not on the back-end side, some logic should be implemented on the front-end side, let’s see I will put comments and suggestions where to implement the logic.

  1. You need to put your whole logic in condition, you can change this logic as per your need this is to understand and implement the demo.
// Default filter type
params.filter = params.filter || '7days';

if (params.filter == "7days") {
 // .. put below logic here
}

  1. The $match stage should be the first stage in your aggregation query, I would suggest a range filter on the createdAt property.
    • There are lots of benefits, where this will use an index on createdAt property
    • I would suggest you create an index on createdAt property, along with other filters if there are.
    • I have implemented the logic for start-date and end-date
    • I would recommend you implement this range logic on the front-end side, so they will pass start-date and end-date in iso date format instead of the filter name, this will reduce the API side load and extra logic. Whatever you choose I have implemented here for this demo.
    • You can change the logic as per your need.
const currentDate = new Date();
let startDate = new Date(currentDate);
let endDate = new Date(currentDate);
// calcualte start date
startDate.setDate(currentDate.getDate() - currentDate.getDay());
// calculate end date
endDate.setDate(currentDate.getDate() + (6 - currentDate.getDay()));
// set hours
startDate.setHours(0, 0, 0, 0);
endDate.setHours(23, 59, 59, 999);
  • The match stage should be something like this:
// Date range filter
let pipeline = [
    { 
        $match: {
            createdAt: {
                $gte: new Date(startDate),
                $lte: new Date(endDate)
            }
        } 
    }
];

  1. Get to count on the base of $group logic
    • Let’s implement the logic for “This Week”, i don’t know the requirement of other options, so keep it in last if you provide more information will implement it.
// You need to specify a timezone, as per your business logic,
// - If the front can send the timezone as per the user's region, 
// - or if you want to show the result same to the all audience then put it static
// - you can check more syntax about timezone on documentation of $dayOfWeek
let timezone = params.timezone || "+5030"; // +5030 is for IST
pipeline.push({
    $group: {
        _id: {
            $dayOfWeek: {
                date: "$createdAt",
                timezone: timezone
            }
        },
        count: { $sum: 1 }
    }
});

  1. If you execute it will result in the format:
let result = await db.collection.aggregate(pipeline);
// result
// [{ _id: 4, count: 3 }, { _id: 6, count: 7 }]

  1. I would recommend you do further logic in your backend instead of query because this kind of logic should be implemented front-end/back-end instead of the query to reduce the extra processing on the database server.
    • A WEEK_DAYS constant for the final API response should be, you need to merge the query result with this.
    • I have implemented the logic, but this is just for the idea, you can change it as per your need and performance best logic.
    • I would suggest you can implement this logic in front-end side so, can reduce more process time in API/back-end side.
const WEEK_DAYS = [
    { day: "Sunday", count: 0 },
    { day: "Monday", count: 0 },
    { day: "Tuesday", count: 0 },
    { day: "Wednesday", count: 0 },
    { day: "Thursday", count: 0 },
    { day: "Friday", count: 0 },
    { day: "Saturday", count: 0 }
];

// convert result into key-value pair, so we can access it quickly in final logic
let result = [{ _id: 4, count: 3 }, { _id: 6, count: 7 }];
resultObj = {};
result.forEach(r => {
    resultObj[r._id] = r.count;
})

// now generate the final result
let finalResult = WEEK_DAYS.map((day, i) => {
    if (resultObj[i+1]) day.count = resultObj[i+1];
    return day;
});

console.log(finalResult);
/*
[
    { "day": "Sunday", "count": 0 },
    { "day": "Monday", "count": 0 },
    { "day": "Tuesday", "count": 0 },
    { "day": "Wednesday", "count": 3 },
    { "day": "Thursday", "count": 0 },
    { "day": "Friday", "count": 7 },
    { "day": "Saturday", "count": 0 }
]
*/

Note: Now you will understand the implementation, This is just a Pseudocode to understand, you can implement your logic as per your requirement.

3

@turivishal, you are on fire. B-)

I am not able to keep up reading your solutions.

Cheers!

1 Like
4

Cheers! @steevej, The combination of NodeJS and MongoDB is my favorite :heart:

1 Like
5

I guess I got too tunnel visioned on getting mongodb to do everything haha. This is a pretty nice solution. Just a few corrections I needed to make since I kept getting a MongoServerError.

let pipeline:any = [
            { 
                $match: {
                    yourFieldHere: {
                        $gte: startDate,
                        $lte: endDate
                    }
                }
            }
        ];

Also I had removed the ones where you had created a new Date for the $gte and $lte operators since it should already be a date. Besides that thank you for your solution. I assume I can also use this for the months of the year or “This Year”.

But for something like last 30 days, would I need to statically declare each day for the month and then iterate over it with the data returned?

6

Ahh got it just corrected it in my previous post.

  • For the “This Year” filter, you can group by monthly count.
  • For the “Last 30 Days”, you can group by daily count.

Depends on How you want to show your data in the graph. Let me know when you finalize the graph, I will try to explain the query.

7

I plan to let the graph data be something as follows:

[
    {
        "day": "Sunday",
        "user_type1": 3,
        "user_type2": 5,
        "user_type3": 6
    },
]

Since I have different types of users a count for each type is necessary so I can have multiple lines for a LineChart that the user can then toggle between to focus on only a subset or 1 and so on.

The Year view should pretty much be the same except I’ll replace “day” with “month” and use the $month operator. After that I’ll use the same concept with the const array of months and iterate through it.

However for the Month view which is basically each day within the last 30 days. The data should look something like:

[
    {
        "date": "03/02/2024",
        "user_type1": 3,
        "user_type2": 5,
        "user_type3": 6
    },
 {
        "date": "04/02/2024",
        "user_type1": 3,
        "user_type2": 5,
        "user_type3": 6
    },
 {
        "date": "05/02/2024",
        "user_type1": 3,
        "user_type2": 5,
        "user_type3": 6
    },
   etc...
]

The date format can always be handled on the client side so formatting wont mater as long as it can be processed