CODEWITHSUNDEEP
dataweavemule4
user12277274
user12277274

Reputation: 109

How to add data based on particular column in an array using dataweave 2.0

I have a json payload of timesheet for a particular employee. I want to groupBy based on ProjectName and also display the period of the time. Below is the input and expected output

Input:

{
    "timeLogs": [
        {
            "projectName": "Project-A",
            "hoursInMins": 480,
            "emailId": "[email protected]",
            "workDate": "04-01-2021"
        },
        {
            "projectName": "Project-B",
            "hoursInMins": 480,
            "emailId": "[email protected]",
            "workDate": "04-01-2021"            
        },
        {
            "projectName": "Project-C",
            "hoursInMins": 180,
            "emailId": "[email protected]",
            "workDate": "04-01-2021"
        },
        {
            "projectName": "Project-A",
            "hoursInMins": 480,
            "emailId": "[email protected]",
            "workDate": "04-02-2021"
        },
        {
            "projectName": "Project-B",
            "hoursInMins": 120,
            "emailId": "[email protected]",
            "workDate": "04-02-2021"
        },
        {
            "projectName": "Project-C",
            "hoursInMins": 360,
            "emailId": "[email protected]",
            "workDate": "04-03-2021"
        }
    ]
}

Expected Output:

{
    "Project-A": 16, //in hours
    "Project-B": 10, //in hours
    "Project-C": 9, //in hours
    "emailId": "[email protected]",
    "fromDate": "04-01-2021",
    "toDate": "04-03-2021",
    "timesheetName": "(04-01-2021 - 04-03-2021)"
}

Thanks in advance

Upvotes: 0

Views: 201

Answers (3)

Imtiyaz Qureshi
Imtiyaz Qureshi

Reputation: 281

Another variation :)

%dw 2.0
output application/json
var sortedDate = payload.timeLogs orderBy ((item) -> item.workDate as Date {format:"dd-MM-yyyy"} )
---
payload.timeLogs groupBy ((item) -> item.projectName)
mapObject ((v, k) -> {
    (k): sum(v map $.hoursInMins)/60
})
++
{
    emailId: sortedDate[0].emailId,
    fromDate: sortedDate[0].workDate,
    toDate: sortedDate[-1].workDate,
    timesheetName: "($(sortedDate[0].workDate) - $(sortedDate[-1].workDate))"
}

Upvotes: 2

aled
aled

Reputation: 25699

Solution:

%dw 2.0
output application/json

fun sumTime(a: Array)=(a reduce ((item, accumulator=0) -> accumulator + item.hoursInMins )) / 60

var totalHourPerProject = payload.timeLogs groupBy $.projectName 
    mapObject ((value, key, index) -> (key): sumTime(value))

var minMaxDates=payload.timeLogs map $.workDate  as Date {format:"MM-dd-yyyy"} orderBy $
---
{
    (totalHourPerProject),
    emailId: payload.timeLogs[0].emailId,
    fromDate: minMaxDates[0],
    toDate: minMaxDates[-1],
    timesheetName: "(" ++ minMaxDates[0] ++ " - " ++ minMaxDates[-1] ++ ")"
}

Upvotes: 2

user3078986
user3078986

Reputation:

Try this:

%dw 2.0
output application/json

var data = {
    "timeLogs": [
        {
            "projectName": "Project-A",
            "hoursInMins": 480,
            "emailId": "[email protected]",
            "workDate": "04-01-2021"
        },
        {
            "projectName": "Project-B",
            "hoursInMins": 480,
            "emailId": "[email protected]",
            "workDate": "04-01-2021"            
        },
        {
            "projectName": "Project-C",
            "hoursInMins": 180,
            "emailId": "[email protected]",
            "workDate": "04-01-2021"
        },
        {
            "projectName": "Project-A",
            "hoursInMins": 480,
            "emailId": "[email protected]",
            "workDate": "04-02-2021"
        },
        {
            "projectName": "Project-B",
            "hoursInMins": 120,
            "emailId": "[email protected]",
            "workDate": "04-02-2021"
        },
        {
            "projectName": "Project-C",
            "hoursInMins": 360,
            "emailId": "[email protected]",
            "workDate": "04-03-2021"
        }
    ]
}



var email = data.timeLogs[0].emailId
var minDate = min(data.timeLogs.*workDate map $ as Date {format: "dd-MM-yyyy"})
var maxDate = max(data.timeLogs.*workDate map $ as Date {format: "dd-MM-yyyy"})

type StringDate = String {format: "dd-MM-yyyy"}
---
(data.timeLogs groupBy $.projectName
mapObject {
    ($$): sum($.hoursInMins) / 60,
     
}) ++ {
    emailId: email,
    fromDate: minDate,
    toDate: maxDate,
    timeSheetName: "($(minDate as StringDate) - $(maxDate as StringDate))"
}

Upvotes: 3

Related Questions