MongoDB 复杂聚合 - 组合求和数数

Question

我有一个数据库，其中每个文档都有一个由许多不同对象组成的数组，我只对其中的 6 个特定对象感兴趣。其中 5 个是整数，1 个是分类（文本）。

为了只留下聚合所需的字段，我在字段数组上使用了 $unwind - 将每个文档乘以它所具有的字段数。之后，我使用基本的 $match 过滤了我想要的特定字段。

这就是我遇到一些麻烦的地方 - 我设法编写了两个查询，每个查询都给出了我需要的最终结果的一半。但我无法将它们组合成一个通用查询。具体来说，我有一个查询提供 5 个整数字段，每个字段都是每个整数字段的 $sum，另一个查询使用分类字段来计算每个类别出现的次数。

所需的输出将为我提供一个包含 5 个 k:v 字段（每个求和计算 1 个）的单个文档，以及一个包含 k:v 字段的附加对象（其中每个键是一个类别，并且value 是它出现的次数，这必须是它自己的对象，因为出现的类别可能会有所不同）。

我添加的示例数据已对其大部分结构进行了条带化​​，并且仅包含与此查询相关的关键部分。这是为了保护我们客户的隐私。

我已经尝试从我能想到的各个角度解决这个问题 - 并且非常感谢任何反馈！

第一个查询：

[{$match: {
     fields: {
      $elemMatch: {
       field_id: 174196148,
       'values.start': {
        $gte: ISODate('2022-02-01T00:00:00.000Z'),
        $lt: ISODate('2022-02-03T00:00:00.000Z')
       }
      }
     }
    }}, {$unwind: {
     path: '$fields'
    }}, {$match: {
     $or: [
      {
       'fields.field_id': 226577699
      },
      {
       'fields.field_id': 225330844
      },
      {
       'fields.field_id': 158472699
      },
      {
       'fields.field_id': 191195626
      },
      {
       'fields.field_id': 219444876
      }
     ]
    }}, {$unwind: {
     path: '$fields.values'
    }}, {$addFields: {
     'Specific - Field Value': {
      $round: [
       {
        $toDecimal: '$fields.values.value'
       }
      ]
     }
    }}, {$group: {
     _id: '$fields.label',
     SumCalculation: {
      $sum: {
       $toDecimal: '$Specific - Field Value'
      }
     }
    }}, {$group: {
     _id: null,
     SumArray: {
      $push: {
       k: '$_id',
       v: '$SumCalculation'
      }
     }
    }}, {$project: {
     _id: 0,
     Final: {
      $arrayToObject: '$SumArray'
     }
    }}]

第二个查询：

 [{$match: {
     fields: {
      $elemMatch: {
       field_id: 174196148,
       'values.start': {
        $gte: ISODate('2022-01-01T00:00:00.000Z'),
        $lt: ISODate('2022-03-31T00:00:00.000Z')
       }
      }
     }
    }}, {$unwind: {
     path: '$fields'
    }}, {$match: {
     'fields.field_id': 177278285
    }}, {$unwind: {
     path: '$fields.values'
    }}, {$group: {
     _id: '$fields.values.value.text',
     ModelCount: {
      $sum: 1
     }
    }}, {$group: {
     _id: null,
     Full: {
      $push: {
       k: '$_id',
       v: '$ModelCount'
      }
     }
    }}, {$project: {
     _id: 0,
     Final: {
      $arrayToObject: '$Full'
     }
    }}]

所需输出：

{
  "Final": {
    "Business Model": [
      {
        "K": "Solar Lease",
        "V": 3
      },
      {
        "K": "Solar Purchase",
        "V": 112
      }
    ],
    "System Size - Signed Contract": 73,
    "Additional Payment for O&M": 2000,
    "O&M Years Included (Paid)": 2,
    "Total Price Including VAT": 396660,
    "1st Milestone - Down Payment": 30280
  }
}

示例数据：

{
  "_id": 1946794344,
  "fields": [
    {
      "type": "money",
      "field_id": 226577699,
      "label": "1st Milestone - Down Payment ",
      "values": [
        {
          "currency": "ILS",
          "value": "6120.0000"
        }
      ],
      "config": {
        "settings": {
          "allowed_currencies": [
            "ILS"
          ]
        },
        "mapping": null,
        "label": "1st Milestone - Down Payment "
      },
      "external_id": "1st-milestone-down-payment-2"
    },
    {
      "type": "money",
      "field_id": 225330844,
      "label": "Additional Payment for O&M",
      "values": [
        {
          "currency": "ILS",
          "value": "0.0000"
        }
      ],
      "config": {
        "settings": {
          "allowed_currencies": [
            "ILS"
          ]
        },
        "mapping": null,
        "label": "Additional Payment for O&M"
      },
      "external_id": "additional-payment-for-om"
    },
    {
      "type": "money",
      "field_id": 158472699,
      "label": "Total Price Including VAT",
      "values": [
        {
          "currency": "ILS",
          "value": "61270.0000"
        }
      ],
      "config": {
        "settings": {
          "allowed_currencies": [
            "ILS"
          ]
        },
        "mapping": null,
        "label": "Total Price Including VAT"
      },
      "external_id": "money"
    },
    {
      "type": "number",
      "field_id": 191195626,
      "label": "System Size - Signed Contract",
      "values": [
        {
          "value": "11.6600"
        }
      ],
      "config": {
        "settings": {
          "decimals": 2
        },
        "mapping": null,
        "label": "System Size - Signed Contract"
      },
      "external_id": "hspq-hmrkt"
    },
    {
      "type": "number",
      "field_id": 219444876,
      "label": "O&M Years Included (Paid)",
      "values": [
        {
          "value": "0.0000"
        }
      ],
      "config": {
        "settings": {
          "decimals": 0
        },
        "mapping": null,
        "label": "O&M Years Included (Paid)"
      },
      "external_id": "om-years-gifted-for-free"
    },
    {
      "type": "category",
      "field_id": 177278285,
      "label": "Business Model",
      "values": [
        {
          "value": {
            "status": "active",
            "text": "Solar Purchase",
            "id": 6,
            "color": "DCEBD8"
          }
        }
      ],
      "external_id": "mvdl-sqy"
    }
  ]
}