display week of day for date stored in mongoDB

I want to display for each day in the last 7 days mount of purchases in the following format:

{
    "sunday":30,
    "monday":20,
    ...
}  

one purchase on the database looks like this:

{
    _id: 603fcbcc691d8a5ecc320059
    productId: "603fc917a569f565687e2626"
    clientId: "1"
    totalPrice: 50
    date: 2021-03-02T00:00:00.000+00:00        // a date object
}

Purchase.aggregate([
                    { "$match": { "date": {$gte: new Date((new Date().getTime() - (7 * 24 * 60 * 60 * 1000))) } } },
                    { "$group": { 
                        "_id": { "day": { $substrCP: [ "$date", 0, 10 ] } },
                        "count": { $sum: 1 }
                    }},
                    { "$sort" : { "_id.day": 1}},
                    
                ])

i run this code and i got:

[
    {
        "_id": {
            "day": "2021-02-28"
        },
        "count": 30
    },
    {
        "_id": {
            "day": "2021-03-01"
        },
        "count": 20
    }
]

Answer

This may help you, but I could not find any readymade method in mongodb which gives me Sunday for value 1. thats why keeping switch statement.

collection.aggregate([
    { "$match": { "date": { $gte: new Date((new Date().getTime() - (7 * 24 * 60 * 60 * 1000))) } } },
    {
        "$group": {
            "_id": { $dayOfWeek: "$date" },
            "count": { $sum: 1 }
        }
    },
    { "$sort": { "_id": 1 } },
    {
        $group: {
            _id: null,
            data: {
                $push: {
                    k: {
                        $switch: {
                            branches: [
                                {
                                    case: { $eq: ["$_id", 1] },
                                    then: "monday"
                                },
                                {
                                    case: { $eq: ["$_id", 2] },
                                    then: "tuesday"
                                },
                                {
                                    case: { $eq: ["$_id", 3] },
                                    then: "wednesday"
                                },
                                
{
                                    case: { $eq: ["$_id", 4] },
                                    then: "thursday"
                                },
                               
 {
                                    case: { $eq: ["$_id", 5] },
                                    then: "friday"
                                },
                                
{
                                    case: { $eq: ["$_id", 6] },
                                    then: "saturday"
                                },

                                {
                                    case: { $eq: ["$_id", 7] },
                                    then: "sunday"
                                }]
                        }
                    },
                    v: "$count"
                }
            }
        }
    },
    {
        $project: {
            data: { $arrayToObject: "$data" }
        }
    }
])

Leave a Reply

Your email address will not be published. Required fields are marked *