javascript - Mongo - 基于名称的值总和的聚合

Question

我正在尝试执行聚合函数以根据名称值计算成本和利润值的总和。因此，如果多个结果的名称为“Fake Provider”，我想要成本结果的总和和 margin 结果的总和。

查询语句:

Pharmacy.aggregate([
{
  $match: {
    $and: [{ 'prescription.status': 'Ready for Pickup' }]
  }
},
{
  $project: {
    'insurance.primary.name': 1,
    'prescription.financial.cost': 1,
    'prescription.financial.margin': 1
  }
}
])

结果类似于:

[
  {
      "_id": "5cab98cd293bd54e94c40461",
      "insurance": {
          "primary": {
              "name": "Fake Provider 1"
          }
      },
      "prescription": [
          {
              "financial": {
                  "cost": "2.89",
                  "margin": "5.60"
              }
          },
          {
              "financial": {
                  "cost": "0.88",
                  "margin": "1.24"
              }
          }
      ]
  },
  {
      "_id": "5cab98d0293bd54e94c40470",
      "insurance": {
          "primary": {
              "name": "Fake Provider 1"
          }
      },
      "prescription": [
          {
              "financial": {
                  "cost": "3.22",
                  "margin": "9.94"
              }
          },
          {
              "financial": {
                  "cost": "2.57",
                  "margin": "9.29"
              }
          },
          {
              "financial": {
                  "cost": "2.03",
                  "margin": "10.17"
              }
          }
      ]
  }
]

我曾尝试创建一个组语句，但没有任何运气。此外，成本和 margin 值当前存储为字符串。

  $group: {
    _id: '$insurance.primary.name',
    Financial: {
      $push: {
        id: '$insurance.primary.name',
        name: '$insurance.primary.name',
        cost: '$prescription.financial.cost',
        margin: '$prescription.financial.margin'
      }
    }
  }

我想得到类似于以下的结果:

 [
{
  "primaryInsurance": "Fake Provider 1",
  "totalFinancialCost": "11.59",
  "totalFinancialMargin": "36.24"
},
{
  "primaryInsurance": "Fake Provider 2",
  "totalFinancialCost": "12.82",
  "totalFinancialMargin": "22.16"
}
]

我想我有一个解决方案，它使用查找和投影返回结果，然后使用 javascript 映射结果并执行加法。但是，我更愿意在数据库级别执行此操作。

Answer 1

您必须先展开“处方”字段，然后执行一组。试试这个管道:

let pipeline = [
{
  $unwind: {
    path: '$prescription'
  }
},
{
  $group: {
    _id: '$insurance.primary.name',
    totalFinancialCost: {
      $sum: { $convert: { input: '$prescription.financial.cost', to: "decimal" } } 
    },
    totalFinancialMargin: {
      $sum: { $convert: { input: '$prescription.financial.margin', to: "decimal" } } 
    }
  }
}]

注意如何将值转换为十进制以执行求和。