温馨提示:本文翻译自stackoverflow.com,查看原文请点击:mongodb - Mongo DB aggregate grouping multiple values that belong to the same document
aggregate aggregate-functions grouping mongodb

mongodb - Mongo DB汇总分组属于同一文档的多个值

发布于 2020-04-10 23:33:17

我有看起来像这样的文件

{
"_id": "5e3334cede31d9555e38dbee",
"time": 400,
"datetime": "2020-01-05T16:35:42.315Z",
"version": "2.0.30",
"hostname": "bvasilchik-lt.extron.com",
"testfile": "cards.txt",
"tests": 5,
"failures": 3,
"skips": 0,
"status": "Failed",
"__v": 0
}

我想创建一个包括每名testfile的时间最多的文件,所以,如果前10名都是相同的结果testfile的名字我只是想显示前一个有相同名称testfile的。

我已经做到了,但是我还想包含另一个字段,该字段还显示与该分组匹配的测试数量,但是我发现的唯一方法是为测试添加$ first或$ last或$ max或$ min字段,但这并不是正确的,因为最高时间可能会有不同数量的测试。

我也在匹配特定日期范围内的结果

const times = await Suite.aggregate([
    {
        "$match": {
            datetime: { "$gte": dateRange.startDate, "$lt": dateRange.endDate, }
        }
    },
    {
        "$group": {
            _id: "$testfile",
            time: { "$max" : "$time" },
        }
    },
    {
        "$sort": {
            time: order
        }
    },
    {
        "$project": {
            _id: 0,
            testfile: "$_id",
            time: "$time"
        }
    }
])

这会产生这些结果

[
    {
    "testfile": "lists.txt",
    "time": 900
    },
    {
    "testfile": "buttons.txt",
    "time": 800
    },
    {
    "testfile": "cards.txt",
    "time": 400
    },
    {
    "testfile": "popover.txt",
    "time": 300
    },
    {
    "testfile": "about-pages.neb",
    "time": 76
    }
]

但是我想要它返回的是

[
    {
    "testfile": "lists.txt",
    "tests": 5, 
    "time": 900
    },
    {
    "testfile": "buttons.txt",
    "tests": 4,
    "time": 800
    },
    {
    "testfile": "cards.txt",
    "tests": 8,
    "time": 400
    },
    {
    "testfile": "popover.txt",
    "tests": 1,
    "time": 300
    },
    {
    "testfile": "about-pages.neb",
    "tests": 2,
    "time": 76
    }
]

查看更多

提问者
BarretV
被浏览
63
Valijon 2020-02-04 06:41

您需要在$group$project阶段添加额外的字段

您需要$maxtime字段使用运算符并累积tests字段time:tests值。在最后阶段,我们$reduce tests以最高的价值

{
  "$group": {
    _id: "$testfile",
    time: {
      $max: "$time"
    },
    tests: {
      "$push": {
        time: "$time",
        tests: "$tests"
      }
    }
  }
},
{
  "$sort": {
    time: 1
  }
},
{
  "$project": {
    _id: 0,
    testfile: "$_id",
    time: "$time",
    tests: {
      $reduce: {
        input: "$tests",
        initialValue: 0,
        in: {
          $add: [
            "$$value",
            {
              $cond: [
                {
                  $and: [
                    {
                      $eq: [
                        "$time",
                        "$$this.time"
                      ]
                    },
                    {
                      $gt: [
                        "$$this.tests",
                        "$$value"
                      ]
                    }
                  ]
                },
                {
                  $subtract: [
                    "$$this.tests",
                    "$$value"
                  ]
                },
                0
              ]
            }
          ]
        }
      }
    }
  }
}

蒙哥运动场