如何使用JSONata重构JSON响应？bookingvalue求和字段缺失问题

原始数据 #

{
    "response": [
        {
            "enterpriseid": 53898,
            "enterprise": " Absolute F and B Facilities Management LLC ",
            "bookingvalue": 46
        },
        {
           
            "enterpriseid": 53898,
            "enterprise": " Absolute F and B Facilities Management LLC ",
            "bookingvalue": 275
        },
        {
            "enterpriseid": 53898,
            "enterprise": " Absolute F and B Facilities Management LLC ",
            "bookingvalue": 199.5
            
        },
        {
            "enterpriseid": 53899,
            "enterprise": " testing Buyer ",
            "bookingvalue": 200
            
        }
       
      
    ]
}

期望输出 #

{
    "output": [
        {
            "enterpriseid": 53898,
            "enterprise": " Absolute F and B Facilities Management LLC ",
            "bookingvalue": 520.5
        },
        {
            "enterpriseid": 53899,
            "enterprise": " testing Buyer",
            "bookingvalue": 200
        }
    ]
}

问题原因 #

你之前的查询存在上下文混淆：在$filter的回调函数中，enterpriseid会优先指向回调内的$item.enterpriseid，而非外层当前对象的enterpriseid，导致求和逻辑无法正确匹配同组数据。

正确查询方案 #

方案一：按enterpriseid+enterprise分组（确保名称一致） #

{
  "output": response $group by $join([enterpriseid, enterprise]) (
    {
      "enterpriseid": enterpriseid,
      "enterprise": enterprise,
      "bookingvalue": $sum(bookingvalue)
    }
  )
}

方案二：仅按enterpriseid分组（已知enterpriseid与enterprise一一对应） #

{
  "output": response $group by enterpriseid (
    {
      "enterpriseid": $first(enterpriseid),
      "enterprise": $first(enterprise),
      "bookingvalue": $sum(bookingvalue)
    }
  )
}

逻辑说明 #

• $group by 是JSONata专门用于分组聚合的语法，自动将相同分组键的元素归为一组
• 分组后直接调用$sum(bookingvalue)即可对组内所有bookingvalue求和
• $first() 用于提取组内第一个元素的enterprise值，因为同组的enterprise应该完全一致

内容的提问来源于stack exchange，提问作者Iliyas Ahmed Farooqui