在mongodb中汇总$ sum [英] Aggregate with $sum in mongodb

问题描述

我在worksheets集合中有数据，如下所示:

I have data in worksheets collection like below:

/* 1 */
{
    "_id" : ObjectId("5c21d780f82aa31334ab6506"),
    "isBilling" : true,
    "hours" : 6,
    "userId" : ObjectId("5c1f38a1d7537d1444738467"),
    "projectId": ObjectId("5c1f38a1d7537d1444731234");
}

/* 2 */
{
    "_id" : ObjectId("5c21d780f82aa31334ab6507"),
    "isBilling" : true,
    "hours" : 4,
    "userId" : ObjectId("5c1f38a1d7537d1444738493"),
    "projectId": ObjectId("5c1f38a1d7537d1444734567");
}

/* 3 */
{
    "_id" : ObjectId("5c21e10fae07cc1204a5b647"),
    "isBilling" : false,
    "hours" : 8,
    "userId" : ObjectId("5c1f388fd7537d1444738492"),
    "projectId": ObjectId("5c1f38a1d7537d1444731234");
}

我正在使用以下汇总查询来获取字段总数:

I am using below aggregate query to get total count of fields:

Worksheet.aggregate([
  {
    $match: conditions
  },
  {
   "$group": {
      "_id": null,
      "billingHours": {
        "$sum": {
          "$cond": [{ "$eq": ["$isBilling", true] }, "$hours", 0]
        }
      },
      "fixContract": {
        "$sum": {
          "$cond": [{ "$eq": ["$isBilling", true] }, 0, "$hours"]
        }
      }
    }
  }
])

现在我想要唯一的projectId字段的总和.上面的情况是 2 .我通过在上面实现的查询中应用两个$ group来尝试它.但这是行不通的.我想得到如下结果:

Now i want the sum of unique projectId field. It above case it is 2. I tried it by applying two $group in above implemented query. But it is not working. I want to get the result like below:

[
  {
    "_id": null,
    "billingHours": 0,
    "fixContract": 8,
    "totalProjects": 2
  }
]

推荐答案

使用 $addToSet 累加器，然后 $size 运算符计算唯一的projectId

Use $addToSet accumulator and then $size operator to count the number of unique projectId

Worksheet.aggregate([
  { $match: conditions },
  { "$group": {
    "_id": null,
    "billingHours": {
      "$sum": {
        "$cond": [{ "$eq": ["$isBilling", true] }, "$hours", 0]
      }
    },
    "fixContract": {
      "$sum": {
        "$cond": [{ "$eq": ["$isBilling", true] }, 0, "$hours"]
      }
    },
    "projectIds": { "$addToSet": "$projectId" }
  }},
  { "$addFields": { "projectIds": { "$size": "$projectIds" }}}
])

这篇关于在mongodb中汇总$ sum的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！