Take the following JSON. It is an array of objects.
[
{
"objects": [
{
"saleItems": [
{
"itemID": 1,
"saleItemType": "Sale",
"productCode": "072",
"legacyProductCode": "1071",
"legacyCategoryCode": "1071",
"categoryCode": "1071",
"subCategoryCode": "101",
"amount": 12,
"originalAmount": 15,
"netAmount": 10,
"originalNetAmount": 12.5,
"vat": 2.5,
"unitVAT": 1,
"vatRate": 20,
"unitMeasure": "EA",
"unitPrice": 15,
"quantity": 1,
"saleChannel": 0
}
]
}
]
},
{
"objects": [
{
"saleItems": [
{
"itemID": 1,
"saleItemType": "Sale",
"productCode": "072",
"legacyProductCode": "1071",
"legacyCategoryCode": "1071",
"categoryCode": "1071",
"subCategoryCode": "101",
"amount": 12,
"originalAmount": 15,
"netAmount": 10,
"originalNetAmount": 12.5,
"vat": 2.5,
"unitVAT": 1,
"vatRate": 20,
"unitMeasure": "EA",
"unitPrice": 15,
"quantity": 1,
"saleChannel": 0
}
]
}
]
},
{
"objects": [
{
"saleItems": [
{
"itemID": 1,
"saleItemType": "Sale",
"productCode": "072",
"legacyProductCode": "1071",
"legacyCategoryCode": "1071",
"categoryCode": "1071",
"subCategoryCode": "101",
"amount": 12,
"originalAmount": 15,
"netAmount": 10,
"originalNetAmount": 12.5,
"vat": 2.5,
"unitVAT": 1,
"vatRate": 20,
"unitMeasure": "EA",
"unitPrice": 15,
"quantity": 1,
"saleChannel": 0
}
]
}
]
}
]
The example output from this data needs to be:
{
"sales": {
"saleItems": [
{
"saleChannel": "1",
"categoryCode": "1071",
"productCode": "072",
"salesAmountIncludingTax": 11.79,
"salesAmountExcludingTax": 9.82,
"discountAmountIncludingTax": 1.0,
"discountAmountExcludingTax": 0.83,
"salesQuantity": 10.0
}
],
"totalSalesAmountIncludingTax": 11.79,
"totalSalesAmountExcludingTax": 9.82,
"totalSalesQuantity": 10.0
},
"refunds": {
"refundItems": [
{
"saleChannel": "1",
"categoryCode": "1010",
"productCode": "033",
"refundAmountIncludingTax": 11.79,
"refundAmountExcludingTax": 9.82,
"discountAmountIncludingTax": 1.0,
"discountAmountExcludingTax": 0.83,
"refundQuantity": 10.0
}
],
"totalRefundAmountIncludingTax": 11.79,
"totalRefundAmountExcludingTax": 9.82,
"totalRefundQuantity": 10.0
}
}
The complexity lies behind the aggregation/totalling. For example:
Within each element of the payload array, there must be an aggregation of the values based on a value. Every object contains saleItems array of objects. If the saleChannel = 0 and saleItemType = "Sale" and if the productCode = "X" (hasn't been seen before) this means there must be a "summing" of the values for every saleItem that the productCode = X.
Example: So for the first 2 objects there could be a productCode of "001". This means that these values must be totalled. The third objects productCode could be "002", a new object must be created within saleItems array of the output.
Example: (this can also be the same for refunds where the if statements are based if the saleItemType = "Refund")
"sales": {
"saleItems": [
{
productCode 001
salesAmountIncludingTax: **The sum of "amount" field, if saleChannel = 0 && saleItemType = "Sale**
},
{
productCode 002
}
]
}
The totalling of the values must follow this logic:
if saleChannel = 0
group by subCategoryCode
salesAmountIncludingTax = sum(saleItems.amount where saleItemType="Sale")
salesAmountExcludingTax = sum(saleItems.netAmount where saleItemType="Sale")
if saleChannel != 0
group by productCode
salesAmountIncludingTax = sum(saleItems.originalAmount where saleItemType="Sale")
salesAmountExcludingTax = sum(saleItems.originalNetAmount where saleItemType="Sale")
If I understood correctly you need to do something like this:
%dw 2.0
output application/json
fun sumItems(itemsToSum, field1, field2) = do {
var item = itemsToSum[0]
var discounts = itemsToSum.priceAdjustments
---
{
"saleChannel": item.saleChannel,
"categoryCode": item.categoryCode,
"subCategoryCode": item.subCategoryCode,
"productCode": item.productCode,
"salesAmountIncludingTax": sum(itemsToSum[field1]),
"salesAmountExcludingTax": sum(itemsToSum[field2]),
"discountAmountIncludingTax": sum(discounts.amount default []),
"discountAmountExcludingTax": sum(discounts.netAmount default []),
"salesQuantity": sum(itemsToSum.quantity)
}
}
var allItems = flatten(payload..*saleItems default [])
var allSaleItems = allItems filter ((item) -> item.saleItemType == "Sale")
var channel0 = allSaleItems filter ((item) -> item.saleChannel == 0)
var otherChannels = allSaleItems filter ((item) -> item.saleChannel != 0)
var summedChannel0 =
channel0
groupBy ((item) -> item.subCategoryCode)
pluck ((itemsToSum, groupName) -> do {
sumItems(itemsToSum, "amount", "netAmount")
})
var summedOtherChannels =
otherChannels
groupBy ((item) -> item.productCode)
pluck ((itemsToSum, groupName) -> do {
sumItems(itemsToSum, "originalAmount", "originalNetAmount")
})
var saleItems = summedChannel0 ++ summedOtherChannels
---
{
sales: {
"saleItems": saleItems,
"totalSalesAmountIncludingTax": sum(saleItems.salesAmountIncludingTax),
"totalSalesAmountExcludingTax": sum(saleItems.salesAmountExcludingTax),
"totalSalesQuantity": sum(saleItems.salesQuantity)
}
}