I have this csv input which has itemnumber as key field as each itemnumber will be assigned with 2 house-id. so i want my json output to be generated as below. where, itemnumber will be unique and under that there should be details which is an array of objects that contains 2 houseid assigned for itemnumber.
Key,Code,Type,ItemNumber,OnHand,Time,quantity,onOrder,houseId,TimeStamp
1,US,F,67,8,10-23-2000,0,0,010,2022-05-04 08:48:11
1,US,F,67,8,10-23-2000,0,0,011,2022-05-04 08:48:11
1,US,F,68,8,10-23-2000,0,0,010,2022-05-04 08:48:11
1,US,F,68,8,10-23-2000,0,0,011,2022-05-04 08:48:11
1,US,F,69,8,10-23-2000,0,0,010,2022-05-04 08:48:11
1,US,F,69,8,10-23-2000,0,0,011,2022-05-04 08:48:11
Expected output in JSON: I want to generated an output where Details will be based on ItemNumber. for each ItemNumber, there will be assigned 2 house ids.
{
"price": {
"Key": "1",
"Code": "US",
"Type": "F",
"Details": [
{
"ItemNumber": "67",
"Date": "10-23-2000",
"Details": [
{
"OnHand": "8",
"Quantity": "0",
"houseDetails": {
"houseId": "010"
}
},
{
"OnHand": "8",
"Quantity": "0",
"houseDetails": {
"houseId": "011"
}
}
]
},
{
"ItemNumber": "68",
"Date": "10-23-2000",
"Details": [
{
"OnHand": "8",
"Quantity": "0",
"houseDetails": {
"houseId": "010"
}
},
{
"OnHand": "8",
"Quantity": "0",
"houseDetails": {
"houseId": "011"
}
}
]
},
{
"ItemNumber": "69",
"Date": "10-23-2000",
"Details": [
{
"OnHand": "8",
"Quantity": "0",
"houseDetails": {
"houseId": "010"
}
},
{
"OnHand": "8",
"Quantity": "0",
"houseDetails": {
"houseId": "011"
}
}
]
}
]
}
}
You can get your required output using groupBy function. This function takes an Array as input, and a function to get the field on which you want to group your payload. So in your case, you need to group your payload on the basis of the ItemNumber key.
You can read more about this function on the official documentation to understand the solution better.
After that you just need a simple map function to map to your required format.
%dw 2.0
output application/json
---
{
Key: payload[0].Key,
Code: payload[0].Code,
"Type": payload[0].Type,
Details: payload groupBy $.ItemNumber
pluck $
map ((item) -> {
ItemNumber: item[0].ItemNumber,
Time: item[0].Time,
Details: item map ((itemDetail) -> {
OnHand: itemDetail.OnHand,
Quantity: itemDetail.quantity,
houseDetails: {
houseId: itemDetail.houseId
}
})
})
}