Source Data : Here is the CSV Data (4 Records):
EMP_ID,DEPT_ID,OPRT_UNIT_CODE,PROD_CODE,NODE_CODE,ALLOC_PCT
123456,00700,0101,000,00700-0101,5
123456,00700,0101,000,00700-0101-000,5
123456,02150,0101,000,2150-0101,95
123456,02150,0101,000,02150-0101-000,95
Below DW Code for your Reference.
Mule - Dataweave Code :
%dw 2.0
output application/json
---
{
data:payload groupBy $.EMP_ID mapObject ((value, key) -> {
empID: value[0].EMP_ID,
jobData: [ value groupBy ($.EMP_ID ++ '|' ++ $.DEPT_ID ++ '|' ++ $.OPRT_UNIT_CODE ++ '|' ++ $.PROD_CODE) mapObject ((subValue, subKey) -> {
// jobData: subKey,
DEPT_ID: subValue[0].DEPT_ID,
OPRT_UNIT_CODE: subValue[0].OPRT_UNIT_CODE,
PROD_CODE: subValue[0].PROD_CODE,
orgData: subValue map ((item, index) -> {
OrgCode: item.NODE_CODE,
AllocPct: item.ALLOC_PCT
})
})
]
})
}
Result - JSON Payload(Error: Duplicate key 'DEPT_ID'): Looks "jobData" data not correct missed "{ }", Experts, request you to please verify and correct the code.
{
"data": {
"empID": "123456",
"jobData": [
{
"DEPT_ID": "00700",
"OPRT_UNIT_CODE": "0101",
"PROD_CODE": "000",
"orgData": [
{
"OrgCode": "00700-0101",
"AllocPct": "5"
},
{
"OrgCode": "00700-0101-000",
"AllocPct": "5"
}
],
"DEPT_ID": "02150",
"OPRT_UNIT_CODE": "0101",
"PROD_CODE": "000",
"orgData": [
{
"OrgCode": "2150-0101",
"AllocPct": "95"
},
{
"OrgCode": "02150-0101-000",
"AllocPct": "95"
}
]
}
]
}
}
Expected Result - JSON Payload: Here is the expected result :
{
"data":{
"empID": "123456",
"jobData": [
{
"DEPT_ID": "00700",
"OPRT_UNIT_CODE": "0101",
"PROD_CODE": "000",
"orgData": [
{
"OrgCode": "00700-0101",
"AllocPct": "5"
},
{
"OrgCode": "00700-0101-000",
"AllocPct": "5"
}
]
},
{
"DEPT_ID": "02150",
"OPRT_UNIT_CODE": "0101",
"PROD_CODE": "000",
"orgData": [
{
"OrgCode": "2150-0101",
"AllocPct": "95"
},
{
"OrgCode": "02150-0101-000",
"AllocPct": "95"
}
]
}
]
}
}
Using mapObject()
is replacing the key-values of the output of groupBy()
by new key-values but that doesn't create new items in an array. Enveloping that output with literal array brackets ([ ... ]
) also doesn't create new items, just an array with the single item object resulting of mapObject()
.
The solution is simply to remove the literal array brackets and replace the use of mapObject()
for pluck()
. The function pluck()
takes the key-values of an object and convert them to elements of an array with the provided mapping. That will give the expected output.