How do I get data of last few days, weeks, months, years in mongodb for admin dashboard?

Working with Data
#1

I have an order collection. I want to get order data of the last 7 days, last 7 weeks, last 7 months and last 7 years. For example, if today is Tuesday, I want to know how many orders did I have on tuesday, how many orders on monday and similarly all days until last tuesday. Similarly I want data for all units I mentioned above. How do I achieve this?

I tried using $addFields with $dayOfTheYear, $week, $month and then grouping it inside facet pipeline accordingly but I ran into some issues cause I can only go within a year. I want a better, more straight forward method that just gives me the result of last 7 units without restriction of current year.

#2

Hi @Anand_S2,

Please provide the following information:

  • Sample documents
  • Expected output
  • Output from what you have attempted
  • Clarification on “for admin dashboard” (As per your post title) - Are you referring to a MongoDB Chart of some sort?
  • MongoDB version

Regards,
Jason

#3

Sample document

{  "_id": {    "$oid": "625d08b94c6acf367a54ef48"  },  "orderId": "PC000002",  "customerId": {    "$oid": "625d0766717de05eb3d9efb8"  },  "franchiseId": {    "$oid": "622c9ad0c576441a1ff392c1"  },  "serviceId": {    "$oid": "622bfbc867851da6b0341d0c"  },  "timeSlot": "01:00 PM - 02:00 PM",  "price": 999,  "mode": "COD",  "status": "Completed",  "address": {    "_id": "625d08a9420441a445605863",    "name": "Ajay Nellikode",    "mobile": 7799627766,    "house": "Pullat House ",    "street": "Punkunnam, Thrissur, Thrissur, Kerala",    "city": "Thrissur",    "state": "Kerala",    "pincode": 680002,    "landmark": "punkunnam Railway Station ",    "type": "Home-1",    "location": [      10.535196599999999,      76.20149140000001    ],    "user": "625d0766717de05eb3d9efb8",    "isDefault": true,    "createdAt": "2022-04-18T06:43:53.389Z",    "updatedAt": "2022-04-18T06:43:53.772Z",    "__v": 0  },  "addOn": [],  "date": "2022-04-18",  "discountAmount": 0,  "grandTotal": 999,  "createdAt": {    "$date": {      "$numberLong": "1650264249642"    }  },  "updatedAt": {    "$date": {      "$numberLong": "1677146342875"    }  },  "__v": 0,  "completedReport": {    "serviceId": {      "$oid": "622bfbc867851da6b0341d0c"    },    "addOn": [],    "grandTotal": 999,    "completedBy": {      "name": "adminVendor",      "username": "adminVendor",      "role": "admin",      "userId": "622c6f86c576441a1ff38cc7",      "phone": "+919605795642"    }  },  "workerId": {    "$oid": "622daaacc525e950351ed9a5"  }}

Expected Output

{
// only count documents which have status as "Completed'
orderCountsOfLastSevenDays: { tue: 1, wed: 3, thu: 5, fri: 3, sat: 7, sun: 10, mon: 6, tue: 5 },
orderCountsOfLastSevenMonths: { oct: 20, nov: 30, dec: 35, jan: 60, feb: 30, mar: 30, apr: 43 },
//a week is from monday to sunday
orderCountsOfLastSevenWeeks: { week1: 10, week2: 15, week3: 13, week4: 20, week5: 10, week6: 21, week7: 25 }
}

//or it can be as simple as each orderCounts being an array of numbers without those keys

Output from what I’ve attempted
I am not getting the desired output, but this is my attempted code:

    salesData() {
        return new Promise((resolve, reject) => {
            const pipeline: PipelineStage[] = [
                {
                    $addFields: {
                        day: {
                            $dayOfYear: '$createdAt'
                        },
                        week: {
                            $week: '$createdAt'
                        },
                        month: {
                            $month: '$createdAt'
                        },
                        year: {
                            $year: '$createdAt'
                        },
                        currentDay: {
                            $dayOfYear: new Date()
                        },
                        currentWeek: {
                            $week: new Date()
                        },
                        currentMonth: {
                            $month: new Date()
                        },
                        currentYear: {
                            $year: new Date()
                        },
                        dayRangeStart: {
                            $cond: {
                                if: {
                                    $and: [
                                        {
                                            $eq: ['$year', '$currentYear']
                                        },
                                        {
                                            $gte: [
                                                {
                                                    $subtract: ['$currentDay', 7]
                                                },
                                                0
                                            ]
                                        }
                                    ]
                                },
                                then: {
                                    $subtract: ['$currentDay', 7]
                                },
                                else: 0
                            }
                        },
                        weekRangeStart: {
                            $cond: {
                                if: {
                                    $gte: [
                                        {
                                            $subtract: ['$currentWeek', 7]
                                        },
                                        0
                                    ]
                                },
                                then: {
                                    $subtract: ['$currentWeek', 7]
                                },
                                else: 0
                            }
                        },
                        monthRangeStart: {
                            $cond: {
                                if: {
                                    $gte: [
                                        {
                                            $subtract: ['$currentMonth', 7]
                                        },
                                        0
                                    ]
                                },
                                then: {
                                    $subtract: ['$currentMonth', 7]
                                },
                                else: 0
                            }
                        }
                    }
                },
                {
                    $facet: {
                        daily: [
                            {
                                $match: {
                                    day: {
                                        $gt: '$dayRangeStart'
                                    },
                                    status: Statuses_Enum.COMPLETED
                                }
                            },
                            {
                                $group: {
                                    _id: 'day',
                                    total: {
                                        $sum: '$grandTotal'
                                    }
                                }
                            }
                        ]
                    }
                }
            ]
            Order.aggregate(pipeline)
            .then((res) => resolve(res))
            .catch((err) => reject(err))
        })
    }

Clarification on ‘for admin dashboard’
I am not refering to MongoDB charts, I was just saying this data is for charts in a dashboard for our business

MongoDB version: 6.0.4