DataWeave groupBy with maxBy

Question

I'm a bit of a DataWeave newb. I'm trying to grab the latest deduction records for a user but also group by the type in the array. This is the data I have to work with:

"users": [
    {
        "employeeId": "123456",
        "lastName": "smith",
        "firstName": "joe ",
        "deductions": [
            {
                "deductionType": "ABC",
                "Amt": 1000,
                "StartDate": "2001-01-02T00:00:00"
            },
            {
                "deductionType": "ABC",
                "Amt": 1000,
                "StartDate": "2019-01-02T00:00:00"
            },
            {
                "deductionType": "ABC",
                "Amt": 1000,
                "StartDate": "2016-01-02T00:00:00"
            },
            {
                "deductionType": "DCA",
                "Amt": 4000,
                "StartDate": "2019-11-02T00:00:00",
            }
        ]
    }

I've tried to go with a similar solution posted on: Dataweave 2.0 maxBy and filter But that doesn't seem to work as I'm getting a null payload.

The end result should look like this:

    "users": [
    {
        "employeeId": "123456",
        "lastName": "smith",
        "firstName": "joe ",
        "deductions": [
            {
                "deductionType": "ABC",
                "Amt": 1000,
                "StartDate": "2019-01-02T00:00:00"
            },
            {
                "deductionType": "DCA",
                "Amt": 4000,
                "StartDate": "2019-11-02T00:00:00",
            }
        ]

My current try to the solution is:

    payload.users map {($),
               deductions: (($.deductions groupBy $.deductionType) mapObject (value, key) -> 
                            {(key) : (value maxBy $.benefitStartDate)}) pluck (value) -> value

}

But this isn't working either.

Answer 1

Here's a different take to the solution, use the one that performs the better :). Comments with the algorithm can be seen in the DW expression. Moreover, I hardcode your sample data in the code so you only need to copy and paste to convince that the DW expression works.

%dw 2.0
output application/dw

var data = "users": [
    {
        "employeeId": "123456",
        "lastName": "smith",
        "firstName": "joe ",
        "deductions": [
            {
                "deductionType": "ABC",
                "Amt": 1000,
                "StartDate": "2001-01-02T00:00:00"
            },
            {
                "deductionType": "ABC",
                "Amt": 1000,
                "StartDate": "2019-01-02T00:00:00"
            },
            {
                "deductionType": "ABC",
                "Amt": 1000,
                "StartDate": "2016-01-02T00:00:00"
            },
            {
                "deductionType": "DCA",
                "Amt": 4000,
                "StartDate": "2019-11-02T00:00:00",
            }
        ]
    }
]
---
users: data.users map do {
    // Sort by StartDate, I type casted to a `DateTime` instead of comparing strings
    // Reverse the sorted list such that the latest dates are at the top of the list
    // Finally, get a set out the list where uniqueness is the deductionType
    //   since distinctBy maintains the first element that matches and removes the rest
    //   you know have a list of distinct deductionType with the latest date.       
    var ds = ($.deductions orderBy ($.StartDate as DateTime))[-1 to 0]
              distinctBy (d) -> d.deductionType
    ---
    {
        ($ - "deductions"),
        deductions: ds
    }
}

Answer 2

Try this

{ 
    users: payload.users map {
        ($ - "deductions"),
        deductions: (($.deductions groupBy $.deductionType) pluck $) map {
            ($ maxBy $.StartDate)
        }
    }
}