Warm tip: This article is reproduced from stackoverflow.com, please click
aggregate aggregate-functions grouping mongodb

Mongo DB aggregate grouping multiple values that belong to the same document

发布于 2020-04-08 09:24:32

I have documents that look like this

{
"_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
}

I want to create a result that includes the documents that have the highest number of time per testfile name, so if the top 10 were all the same testfile name I'd only want to show the top one that had the same testfile name.

I have done this but I also wanted to include another field that also shows the number of tests matching that grouping, but the only ways I found were to add the $first or the $last or the $max or the $min for the tests field, but that wouldn't be correct b/c the highest time might have a different number of tests.

I am also matching results from a specific date range

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"
        }
    }
])

this produces these results

[
    {
    "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
    }
]

but what I want it to return is

[
    {
    "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
    }
]
Questioner
BarretV
Viewed
57
分享
Valijon 2020-02-04 06:41

You need to add extra field into $group and $project stages.

You need to use $max operator for time field and accumulatetests field time:tests values. In the last stage, we $reduce tests field taking highest value

{
  "$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
              ]
            }
          ]
        }
      }
    }
  }
}

MongoPlayground

BarretV 2020-02-03 23:03:29

I had originally tried that but ran into the issue where the testfile with the highest time might not have the highest amount of tests, reason being is the test was edited now it has less or more tests than prior. I want to know the test file with the highest time and know how many tests were in that testfile here is an example with more data mongoplayground.net/p/1n7v8U1UmmO the test with the time "time": 76.404574 has 4 tests but the query results in 5 tests since that was the max that the testfile about-pages.neb has

Valijon 2020-02-04 00:51:03

@BarretV check again please, I've updated my answer

BarretV 2020-02-04 05:58:24

I tried it on the mongoplayground mongoplayground.net/p/mhIU5lvD5lC and it still shows up about-pages.neb having a time of 76.404574 which is the correct highest time, but if you look at the document that has that time it only has 4 for tests, but the aggregate shows tests with the value of 5. I guess it's hard to find the document that had the matching time and grab the value of the test from that for the aggregation

Valijon 2020-02-04 06:42:27

@BarretV check again please, updated

Valijon 2020-02-05 05:02:40

@BarretV Cool. Good luck

Related issues

1
Mongoose: CastError at path "_id" when ID is not being called
2
Mongoose: How to get documents that their subdocument includes a value ( Many to one relationship )
3
How to count specific documents in a row using aggregation?
4
Mongo db Collection find returns from the first entry not from last from client side
5
MongoDB object type is not saved
6
MongoDB $lookup and $map array of objects
7
Nodejs Expressjs Mongodb Javascript Unit Test Using Mocha Chai Sinon for random Nodejs Project
8
Using Aggregate function in MongoDB to Divide and Mutliply
9
mongorestore uncaught exception: syntax error
10
How to get deleted documents from DeleteMany middleware in Mongoose?