GroupBy multiple columns in MongoDB

Question

I have a shifts collection like below

{
    "_id" : ObjectId("5885a1108c2fc432d649647d"),
    "from" : ISODate("2017-01-24T06:21:00.000Z"), //can be weekday, sat, sun
    "to" : ISODate("2017-01-24T08:21:00.000Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32 //this wil vary based on **from** field
}

{
    "_id" : ObjectId("5885a1108c2fc432d649647e"),
    "from" : ISODate("2017-01-25T06:21:00.000Z"),
    "to" : ISODate("2017-01-25T08:21:00.000Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32
}

{
    "_id" : ObjectId("5885a1108c2fc432d649647f"),
    "from" : ISODate("2017-01-26T06:21:00.000Z"),
    "to" : ISODate("2017-01-26T08:21:00.000Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32
}

I want to be able to produce an output like this

Job A (This can be obtained using jobId)

• Weekday earnings - $50 (2 * $25)
• Saturday earnings - $90 (3 * $30)
• Sunday earnings - $100 (2 * $50) Total earnings = $240

Job B

• Weekday earnings..............

Total earnings = Job A + Job B ..... = $X

Weekday or Saturday or Sunday can be derived from from field. For a Weekday, all days from Monday to Friday need to be grouped. Number of hours can be derived by subtracting from from to.

So, I think the following needs to be done, but I am not able to implement in MongoDB

• Group by jobId, group by day extracted from from (also group all weekdays) and get the sum of hours extracted by subtracting from and to.
• Finally, get the sum of all the above sums to get the total earnings
• Get the final sum of all job earnings.

I found a few similar questions but I am not able to apply it to my situation
mongodb group values by multiple fields
Mongodb Aggregation Framework | Group over multiple values?
How to group by multiple columns and multiple values in mongodb

USE CASE:
When a user visits a page called earnings, I need to show him the earnings for the last week for each job and total earnings (then he can change the date range). So, I intent the show the split up for each Job, further split by weekday, saturday and sunday earnings and total earnings and work hours for THAT job. And final total earnings which is a summation of all the individual job earnings.

P.S I am using MongoDB 3.4

Answer 1

Follow the below aggregation query:

db.shifts.aggregate([{ 
    //this get the day of week and converts them into sunday, saturday
    $project: {
        jobId:1,
        hourlyRate:1, 
        dayOfWeek: { $dayOfWeek: "$from" }, 
        workedHours: {$divide:[{ $subtract: ["$to", "$from"] }, 3600000]}, 
        saturday:{$floor: {$divide:[{ $dayOfWeek: "$from" }, 7]}},
        sunday:{$floor: {$divide:[{$abs:{$subtract:[{ $dayOfWeek: "$from" }, 7]}}, 6]}},
    }
}, {
    //based on the values of sunday and saturday gets the value of weekday
    $project: {
        jobId:1,
        workedHours:1,
        hourlyRate:1,
        saturday:1,
        sunday: 1,
        weekday:{$abs: {$add:["$sunday","$saturday", -1]}},
    } 
}, {
    //here calculates the earnings for each job
    $group:{
        _id:"$jobId",
        sundayEarnings:{$sum: {$multiply:["$sunday", "$hourlyRate", "$workedHours"]}},
        saturdayEarnings:{$sum: {$multiply:["$saturday", "$hourlyRate", "$workedHours"]}},
        weekdayEarnings:{$sum: {$multiply:["$weekday", "$hourlyRate", "$workedHours"]}},
        totalEarnings: {$sum:{$multiply:["$hourlyRate", "$workedHours"]}},
        totalWorkedHours: {$sum: "$workedHours"}
    }
}, {
    //and finally calculates the total jobs earnings
    $group:{
        _id:null,
        jobs:{$push:{
            jobId: "$_id",
            sundayEarnings: "$sundayEarnings",
            saturdayEarnings: "$saturdayEarnings",
            weekdayEarnings: "$weekdayEarnings",
            totalEarnings: "$totalEarnings",
            totalWorkedHours: "$totalWorkedHours"
        }},
        totalJobsEarning: {$sum: "$totalEarnings"}
    }
}])

1. The first $project aggregation gives either 0 or 1 values to saturday and sunday based on the dayOfWeek value by making several arithmetic calculations.
2. Second $project aggregation calculates the weekday's value based on the saturday and sunday values.
3. The first $group calculates the earnings for each day in each job.
4. Finally the second $group aggregation calculates the sum of earnings of all the jobs.

Test

This is my input:

{
    "_id" : ObjectId("5885a1108c2fc432d649647d"),
    "from" : ISODate("2017-01-24T06:21:00Z"),
    "to" : ISODate("2017-01-24T08:21:00Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32
}
{
    "_id" : ObjectId("5885a1108c2fc432d649647e"),
    "from" : ISODate("2017-01-25T06:21:00Z"),
    "to" : ISODate("2017-01-25T08:21:00Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32
}
{
    "_id" : ObjectId("5885a1108c2fc432d649647f"),
    "from" : ISODate("2017-01-26T06:21:00Z"),
    "to" : ISODate("2017-01-26T08:21:00Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32
}
{
    "_id" : ObjectId("58870cfd59dfb6b0c4eadd72"),
    "from" : ISODate("2017-01-28T06:21:00Z"),
    "to" : ISODate("2017-01-28T08:21:00Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32
}
{
    "_id" : ObjectId("58870dc659dfb6b0c4eadd73"),
    "from" : ISODate("2017-01-29T06:21:00Z"),
    "to" : ISODate("2017-01-29T08:21:00Z"),
    "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
    "hourlyRate" : 32
}

The above aggregation query gives the following output:

{
    "_id" : null,
    "jobs" : [
        {
            "jobId" : ObjectId("586d7d6acfc7e05669d6e2c8"),
            "sundayEarnings" : 64,
            "saturdayEarnings" : 64,
            "weekdayEarnings" : 192,
            "totalEarnings" : 320,
            "totalWorkedHours" : 10 
        }
    ],
    "totalJobsEarning" : 320
}

In the jobs array there is only one job because the docs of shifts collection is referenced to the same jobId. You can try this with different jobIds and it will give you different jobs with total earnings.

Answer 2

Using the $switch operator to build up an expression of case statements to evaluate earnings, you can run the following pipeline to get the desired result

var hoursWorked = { 
    "$divide": [
        { "$subtract": ["$to", "$from"] }, 
        3600000
    ]
};

db.collection('shifts').aggregate([
    {
        "$match": {
            "jobId": { "$in": [jobA_id, jobB_id] },
            "from": { "$gte": new Date() }
        }
    },
    {
        "$group": {
            "_id": null,
            "totalEarnings": {
                "$sum": {
                    "$switch": {
                        "branches": [
                            {
                                "case": { 
                                    "$not": { 
                                        "$in": [
                                            { "$dayOfWeek": "$from" }, 
                                            [1, 7] 
                                        ] 
                                    } 
                                }, 
                                "then": { "$multiply": [hoursWorked, 20] }
                            },
                            { 
                                "case": { 
                                    "$eq": [
                                        { "$dayOfWeek": "$from" }, 
                                        7
                                    ] 
                                }, 
                                "then": { "$multiply": [hoursWorked, 25] }
                            },
                            { 
                                "case": { 
                                    "$eq": [
                                        { "$dayOfWeek": "$from" }, 
                                        1 
                                    ] 
                                }, 
                                "then": { "$multiply": [hoursWorked, 30] } 
                            }
                        ]
                        "default": 0
                    }   
                } 
            }              
        }
    }       
], function(err, results) {
    if (err) throw err;
    console.log(JSON.stringify(results, null, 4));
    console.log(results[0].totalEarnings); 
});