Javascript 带元素数组的Mongo聚合

发布于01月09日

需要进行数据聚合，以汇总根和子表中的数据

具有这样的输入数据

[
  {
    id: ID,
    points: 10,
    sources: [{
      sourceId: 's1',
      sourcePoints: 6,
    },
    {
      sourceId: 's2',
      sourcePoints: 4,
    }]
  }, 
  {
    id: ID,
    points: 2,
    sources: [{
      sourceId: 's1',
      sourcePoints: 2,
    }]
  }
];

我想得到这个摘要

{
  id: ID,
  points: 12 // sum
  sources: {
    s1: { sourcePoints: 8 } // sum of all s1 from all records for sourceId = s1 and id = ID
    s2: { sourcePoints: 4 } // same as above, for sourceId = s2 and id = ID
    ... // sources can be N
  }
}

对于我来说，从根中求和很简单

[
 { $match: { id: ID } },
 { $group: { _id: $id, sum: { $sum: '$points' }} } 
]

但是我不知道如何从数组中加和

推荐答案

对于您的问题，您可能需要使用$arrayToObject操作符，因为您可能有N个名称未知的信号源.

一种解决办法如下:

db.collection.aggregate([
  {
    $match: {
      id: "ID"
    }
  },
  {
    "$unwind": "$sources"
  },
  {
    $group: {
      _id: {
        mainId: "$id",
        "sourceId": "$sources.sourceId"
      },
      sumPerSoureId: {
        $sum: "$sources.sourcePoints"
      }
    }
  },
  {
    $group: {
      _id: "$_id.mainId",
      totalPoints: {
        $sum: "$sumPerSoureId"
      },
      keyVal: {
        $push: {
          "k": "$_id.sourceId",
          "v": {
            "sourcePoints": "$sumPerSoureId"
          }
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      id: "$_id",
      points: "$totalPoints",
      sources: {
        "$arrayToObject": "$keyVal"
      }
    }
  }
])

你可以在mongoplayground.号上亲自试一下.

提供的解决方案可能没有针对数百万个文档进行优化，因为我一开始就使用了两个$unwind.