CODEWITHSUNDEEP
mongodbgroup-bymongodb-queryaggregation-frameworkaggregation
Rahul Anand
Rahul Anand

Reputation: 573

Multiple accumulators in MongoDB Group Aggregation query for day,week,month and year

I am trying to group my data day-wise,weekly,monthly and yearly in single pilepline. I have date, week number, month and year in separate fields . But i am not able to figure out how to do group aggregation in a single flow. Below is sample json :

{ 
    "Ctrans" : {
    "status" : "active"
    }, 
    "Day-month" : "25-9", 
    "Month" : 9, 
    "Year" : 2019, 
    "Week" : 38
}

My query for this pipeline:

db.getCollection("transaction").aggregate(
    [
        { 
            "$lookup" : {
                "from" : "customer", 
                "localField" : "pB.phone", 
                "foreignField" : "phone", 
                "as" : "Ctrans"
            }
        }, 
        { 
            "$unwind" : {
                "path" : "$Ctrans", 
                "includeArrayIndex" : "arrayIndex", 
                "preserveNullAndEmptyArrays" : false
            }
        }, 
        { 
            "$project" : {
                "date" : {
                    "$dateFromString" : {
                        "dateString" : "$createdAt", 
                        "onError" : "$date"
                    }
                }, 
                "Weekdate" : {
                    "$dateFromString" : {
                        "dateString" : "$createdAt", 
                        "onError" : "$date"
                    }
                }, 
                "Ctrans.status" : 1.0, 
                "createdAt" : 1.0
            }
        }, 
        { 
            "$project" : {
                "date" : {
                    "$dateToParts" : {
                        "date" : "$date"
                    }
                }, 
                "week" : {
                    "$week" : "$Weekdate"
                }, 
                "Ctrans.status" : 1.0
            }
        }, 
        { 
            "$project" : {
                "Day-month" : {
                    "$concat" : [
                        {
                            "$toString" : "$date.day"
                        }, 
                        "-", 
                        {
                            "$toString" : "$date.month"
                        }
                    ]
                }, 
                "Month" : "$date.month", 
                "Year" : "$date.year", 
                "Week" : "$week", 
                "Ctrans.status" : 1.0
            }
        }, 
        { 
            "$group" : {
                "_id" : {
                    "Today" : "$Day-month", 
                    "Status" : "$status"
                }, 
                "Total" : {
                    "$sum" : 1.0
                }
            }
        }
    ], 
    { 
        "allowDiskUse" : false
    }
);

I aim to groupby with pairs of : (Day-month, status),(month,status),(year,status),(week,status) and get count of each paired group separately.

Upvotes: 1

Views: 978

Answers (1)

mickl
mickl

Reputation: 49975

You can use $facet to apply separate $group stages on your data set, try:

db.collection.aggregate([
    // current aggregation stages,
    {
        $facet: {
            "dayMonthStatus": [
                { $group: { _id: { status: "$Ctrans.status", "dayMonth": "$Day-month" }, count: { $sum: 1 } } }
            ],
            "monthStatus": [
                { $group: { _id: { status: "$Ctrans.status", "month": "$Month" }, count: { $sum: 1 } } }
            ],
            "yearStatus": [
                { $group: { _id: { status: "$Ctrans.status", "year": "$Year" }, count: { $sum: 1 } } }
            ],
            "weekStatus": [
                { $group: { _id: { status: "$Ctrans.status", "week": "$Week" }, count: { $sum: 1 } } }
            ]
        }
    }
])

Upvotes: 1

Related Questions