Mule 4 - How to combine arrays inside a nested array with the same id field into one

Question

Suppose I have the following payload with nested array, how do I combine the array inside the nested array for the same externalId as well as some logic on certain field like

shipQty - this field will be sum or add up for records with the same externalId under fillingOrder

serialNumbers - all the records under serialNumbers will be display together if the externalId is same

Kindly refer below for the input and expected output

Json Payload Input

     {
        "Identifier": "9i098p-898j-67586k",
        "transactionDate": "2019-09-08T10:01:00-04:00",
        "order": [
            {
                "orderNumber": "123456789",
                "CourierOrderId": "1300-88-2525",
                "fillingOrder": [
                    {
                        "numberOfBoxes": 0,
                        "tracking": [
                            {
                                "carrier": "Orange",
                                "trackNum": "3333444",
                                "trackUrl": "https://www.orange.com/track/status",
                                "shipDate": "2019-09-08T10:01:00-04:00",
                                "SerialNumber": "00000123"
                            }
                        ],
                        "row": [
                            {
                                "externalId": "1",
                                "unitNo": "OP04-123456-789",
                                "shipQty": 2,
                                "serialNumbers": [
                                    {
                                        "serialNumber": "USD333555",
                                        "quantity": 1
                                    },
                                    {
                                        "serialNumber": "USD235678",
                                        "quantity": 1
                                    }
                                ]
                            }
                        ]
                    },
                    {
                        "tracking": [
                            {
                                "carrier": "Apple",
                                "trackNum": "555666",
                                "trackUrl": "https://www.apple.com/track/status",
                                "shipDate": "2019-09-08T10:01:00-04:00",
                                "SerialNumber": "00000645"
                            }
                        ],
                        "row": [
                            {
                                "externalId": "1",
                                "unitNo": "OP04-123456-789",
                                "shipQty": 3,
                                "serialNumbers": [
                                    {
                                        "serialNumber": "USD123456",
                                        "quantity": 1
                                    },
                                    {
                                        "serialNumber": "USD98765",
                                        "quantity": 1
                                    },
                                    {
                                        "serialNumber": "USD45689",
                                        "quantity": 1
                                    }
                                    
                                ]
                            }
                        ]
                    },
                    {
                        "tracking": [
                            {
                                "carrier": "banana",
                                "trackNum": "587390",
                                "trackUrl": "https://www.banana.com/track/status",
                                "shipDate": "2019-09-08T10:01:00-04:00",
                                "SerialNumber": "00000365"
                            }
                        ],
                        "row": [
                            {
                                "externalId": "2",
                                "unitNo": "OP05-123456-111",
                                "shipQty": 2,
                                "serialNumbers": [
                                    {
                                        "serialNumber": "USD00045",
                                        "quantity": 1
                                    },
                                    {
                                        "serialNumber": "USD00046",
                                        "quantity": 1
                                    }
                                    
                                ]
                            }
                        ]
                    }
                ]
            }
        ]
    }

Expected Json Output

{
    "row": [
        {
            "externalId": "1",
            "unitNo": "OP04-123456-789",
            "shipQty": 5, //the shipQty should be add up when the externalId is same
            "serialNumbers": [ //the serialNumbers should display all the data inside the serialNumbers when the externalId is same
                {
                    "serialNumber": "USD333555",
                    "quantity": 1
                },
                {
                    "serialNumber": "USD235678",
                    "quantity": 1
                },
                {
                    "serialNumber": "USD123456",
                    "quantity": 1
                },
                {
                    "serialNumber": "USD98765",
                    "quantity": 1
                },
                {
                    "serialNumber": "USD45689",
                    "quantity": 1
                }
            ]
        },
        {
            "externalId": "2",
            "unitNo": "OP05-123456-111",
            "shipQty": 2,
            "serialNumbers": [
                {
                    "serialNumber": "USD00045",
                    "quantity": 1
                },
                {
                    "serialNumber": "USD00046",
                    "quantity": 1
                }
        }
    ]
}

Answer 1

It looks like you only need the data of "row" inside the fillingOrder field of your payload. So first thing to simplicy the problem is to get all the rows as a single array. Once you have that them you just need to group that by external id and the problem will start to look smaller.

%dw 2.0
output application/json

//First get all rows since it looks like you only need them. 
//If you find this confusing try to use flatten with some simpler payloads. 
var allRows = flatten(flatten(payload.order.fillingOrder).row)

//Group them according to external id.
var groupedExtId = allRows groupBy $.externalId
---
{
    row: groupedExtId pluck ((value, extId, index) -> do {
        var sumShipQuant = sum(value.shipQty default [])
        ---
        {
            externalId: (extId), //the key after grouping is external id
            unitNo: value.unitNo[0], //assuming it is same across diff external id
            shipQty: sumShipQuant,
            serialNumbers: flatten(value.serialNumbers) //Flatten because value is an array and it has multiple serielNumbers array
        }
    })
}