在MongoDB中将Aggregate与$ group一起使用 [英] Use Aggregate with $group in mongodb

问题描述

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

I have data in worksheets collection like below:

/* 1 */
{
    "_id" : ObjectId("5c21d780f82aa31334ab6506"),
    "isBilling" : true,
    "hours" : 6,
    "userId" : ObjectId("5c1f38a1d7537d1444738493"),
}

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

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

我必须创建一个汇总查询来汇总小时数，其中isBilling等于true，而isBilling等于false.我想要以下输出:

I have to create a aggregate query to sum the hours, where isBilling equals to true, and where isBilling equals to false.I want the below output:

{
 "billingHours":10,
 "fixContract":8
}

我必须获取具有特定userId的数据.我尝试了以下方法:

Worksheet.aggregate([
  {
        $match: conditions
  },
  {
      $lookup:{
          "from": "worksheets",
          "let": {},
          "pipeline": [
            { "$match": { "$expr": { "$eq": [ "$isBilling",false] } } },
            { 
              "$group": { "_id": null, "totalHours": { "$sum": "$hours" } }
            },
          ],
          "as": "billingHours"
      }
  },
  {
        "$project":{"billingHours":1}
  }
])

我得到以下结果:

[
    {
        "_id": "5c21d780f82aa31334ab6506",
        "billingHours": [
            {
                "_id": null,
                "totalHours": 16
            }
        ]
    },
    {
        "_id": "5c21d780f82aa31334ab6507",
        "billingHours": [
            {
                "_id": null,
                "totalHours": 16
            }
        ]
    }
]

我不知道为什么它会给我16个小时而不是10个小时，却给我两个对象而不是1个.

I don't know why it is giving me 16 hours instead of 10 and giving me two objects instead of 1.

推荐答案

您无需使用在此处 $lookup .简单的 $group 与<一个href ="https://docs.mongodb.com/manual/reference/operator/aggregation/cond/" rel ="nofollow noreferrer"> $cond 会完成这项工作

You don't need to use $lookup here. Simple $group with $cond will do the job.

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

这篇关于在MongoDB中将Aggregate与$ group一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！