How to aggregate data with particular column using dataweave 2.0

Question

I have a payload with projects and the number of hours details for a particular employee. I want to write a dataweave to consolidate the Hours based on the project. Since the no.of hours format is not a number and have different format (eg. 02:00) I am unable to aggregate the hours. Input:

{
    "response": {
        "result": [
            {
                "jobName": "Project1",
                "hours": "08:00"
            },
            {
                "jobName": "Project1",
                "hours": "08:00"
            },
            {
                "jobName": "Project1",
                "hours": "03:00"
            },
            {
                "jobName": "Project2",
                "hours": "08:00"
            },
            {
                "jobName": "Project2",
                "hours": "02:00"
            },
            {
                "jobName": "Project3",
                "hours": "06:00"
            }
        ]
    }
}

Expected Output:

 {
        "Project1" : "19:00",
        "Project2" : "10:00",
        "Project3" : "06:00"
    }

Thanks in advance

Answer 1

Decompose the problem. You need to convert the string to a number; the easiest number, in this case, would be the total number of minutes. Then you need to sum them all.. and finally convert them back, but diving by 60, losing the remainder and then doing a modulo. Remember to format the numbers as string so that we get 0 padding.

%dw 2.0
import sumBy from dw::core::Arrays
output application/json

fun hoursToMinutes(hours: String) : Number =
    (hours[0 to 1] as Number * 60) + (hours[3 to 4] as Number)

fun minutesToHours(minutes: Number) : String = 
    "$(floor(minutes / 60) as String { format: "00" }):$((minutes mod 60) as String {format: "00"})"

fun sumHours(hours) : Number = 
    hours sumBy hoursToMinutes($.hours)
---
payload.response.result 
    groupBy $.jobName 
    mapObject ($$): minutesToHours(sumHours($))

Edit: Changed sumHours to hours sumBy hoursToMinutes($.hours) for better readability; the sumBy is doing a reduce as well so no gains other than readability.