如何在Mule 4中用DataWeave计算订单总价（Qty*Price求和）

问题背景 #

需处理一份CSV文件，要求按PON列分组映射，计算每个订单的总价（Qty与Price相乘后求和），并按订单号排序，但现有DataWeave脚本未输出正确结果。

原始数据与脚本 #

CSV输入数据 #

PON,Item,Qty,Price
PON1000,2015,2,38.08
PON1000,2016,1,33.37
PON1001,2015,2,38.08

现有DataWeave脚本 #

%dw 2.0
output application/json
---
payload groupBy ($.PON) pluck $ map ( () -> {
   "order": $[0].PON default "",
   "total": (sum( $.Price filter ($ != "") ) as Number) as String {format: "##,###.00"},
   "products": $ map {
       "product": $.Item,
       "price": ($.Price as Number) as String {format: "##,###.00"},
       "quantity": $.Qty
   }
})

实际错误结果 #

[
 {
   "order": "PON1000",
   "total": "71.45",
   "products": [
    {
     "product": "2015",
     "price": "38.08",
     "quantity": "2"
    },
    {
     "product": "2016",
     "price": "33.37",
     "quantity": "1"
    }
   ]
   },
   {
    "order": "PON1001",
    "total": "38.08",
    "products": [
    {
      "product": "2015",
       "price": "38.08",
       "quantity": "2"
     }
    ]
    }
   ]

期望结果 #

[
 {
   "order": "PON1000",
   "total": "109.53",
   "products": [
    {
     "product": "2015",
     "price": "38.08",
     "quantity": "2"
    },
    {
     "product": "2016",
     "price": "33.37",
     "quantity": "1"
    }
   ]
   },
   {
    "order": "PON1001",
    "total": "76.16",
    "products": [
    {
      "product": "2015",
       "price": "38.08",
       "quantity": "2"
     }
    ]
    }
   ]

错误原因 #

1. 总价计算逻辑错误：原脚本仅对Price字段直接求和，未考虑与Qty相乘，导致结果未反映实际订单总价
2. 缺少排序步骤：输出结果未显式按order字段排序（当前结果虽顺序正确，但需确保稳定性）

修正后的DataWeave脚本 #

%dw 2.0
output application/json
---
(payload groupBy ($.PON) pluck $ map (groupItems) -> {
   "order": groupItems[0].PON default "",
   "total": (sum(groupItems map ($.Qty as Number) * ($.Price as Number)) as Number) as String {format: "##,###.00"},
   "products": groupItems map {
       "product": $.Item,
       "price": ($.Price as Number) as String {format: "##,###.00"},
       "quantity": $.Qty
   }
}) sortBy ($.order)

关键修改说明 #

• 总价计算：遍历分组内的每个条目，将Qty和Price转为数字后相乘，再对所有乘积求和，得到真实订单总价
• 排序处理：在最终结果数组后添加sortBy($.order)，确保输出按订单号升序排列
• 可读性优化：将匿名参数改为groupItems，让脚本逻辑更清晰

验证结果 #

修正后的脚本输出与期望结果完全一致，满足业务需求。

内容的提问来源于stack exchange，提问作者Jesus Abraham Felix González