1

I have am trying to perform an aggregate function on my collection but I can't seem to fit the right query for the job.

My goal is to display the top 2 fastest laps on all maps and show the associated user first name and last name.

Here is my stats collections:

{
    "_id" : ObjectId("5c86674d87e8cd468c850c86"),
    "lapTime" : "1:32:29",
    "map" : "France",
    "driver" : [ 
        ObjectId("5c7c499b555fa13f50c9c248")
    ],
    "date" : ISODate("2019-03-11T13:49:01.472Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("5c8667ec87e8cd468c850c87"),
    "lapTime" : "2:32:34",
    "map" : "France",
    "driver" : [ 
        ObjectId("5c7c499b555fa13f50c9c248")
    ],
    "date" : ISODate("2019-03-11T13:51:40.895Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("5c86674x87e8Sd567c120c86"),
    "lapTime" : "1:12:29",
    "map" : "France",
    "driver" : [ 
        ObjectId("5c7c499b555fa13f50c9c248")
    ],
    "date" : ISODate("2019-03-11T10:49:01.472Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("5c8667f887e8cd468c850c88"),
    "lapTime" : "1:88:29",
    "map" : "Italy",
    "driver" : [ 
        ObjectId("5c7c499b555fa13f50c9c248")
    ],
    "date" : ISODate("2019-03-11T13:51:52.727Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("5c866970c65910291c6f2000"),
    "lapTime" : "1:34:29",
    "map" : "Italy",
    "driver" : [ 
        ObjectId("5c80f78ca0ecdf26c83dfc8a")
    ],
    "date" : ISODate("2019-03-11T13:58:08.135Z"),
    "__v" : 0
}
{
    "_id" : ObjectId("5c868532b5c50c17b0917f9e"),
    "lapTime" : "1:43:33",
    "map" : "Italy",
    "driver" : [ 
        ObjectId("5c80f78ca0ecdf26c83dfc8a")
    ],
    "date" : ISODate("2019-03-11T15:56:34.869Z"),
    "__v" : 0
}

Since I am passing the driver ID by reference here:
"driver":[ObjectId("5c7c499b555fa13f50c9c248")] , I want to display the driver's attributes from my users collection.

Here is one of my user objects:

{
    "_id" : ObjectId("5c7c499b555fa13f50c9c248"),
    "password" : "$2a$10$L..Pf44/R7yJfNPdikIObe04aiJaY/e94VSKlFscjgYOe49Y7iwJK",
    "email" : "john.smith@yahoo.com",
    "firstName" : "John",
    "lastName" : "Smith",
    "laps" : [],
    "__v" : 0,
}

Here is what I tried so far:

db.getCollection('stats').aggregate([
  { $group: {
    _id: { map: "$map" },   // replace `name` here twice
    laps: { $addToSet: "$lapTime" },
    driver:{$addToSet: "$driver"},
    count: { $sum: 1 } 
  } },
 {$lookup:
       {
         from: "users",
         localField: "firstName",
         foreignField: "lastName",
         as: "driver"
       }}, 
  { $match: { 
    count: { $gte: 2 } 
  } },
  { $sort : { count : -1} },
  { $limit : 10 }
]);

As a result, I am getting drivers as a empty array.

What I am actually trying to achieve is something like this:

{
    "_id" : {
        "map" : "France"
    },
    "laps" : [ 
        "Jonathan Smith":"2:32:34", 
        "Someone Else":"1:32:29"
    ],
    "count" : 2.0
}
1

I think this should work:-

   db.getCollection('stats').aggregate([
         { $unwind: "$driver" },
         {$lookup:
               {
                 from: "users",
                 localField: "driver",
                 foreignField: "_id",
                 as: "driver"
               }},
          { $group: {
            _id: { map: "$map" },   // replace `name` here twice
            laps: { $addToSet: 
                    { 
                      lapTime: "$lapTime", 
                      driverName: "$driver.firstName" + "$driver.lastName" 
                    }
                  },
            count: { $sum: 1 } 
          } }, 
          { $match: { 
            count: { $gte: 2 } 
          } },
          { $sort : { count : -1} },
          { $limit : 10 }
        ]);
  • not exactly, it displays the driver's details in full but it's what I wanted to achieve. I am aiming at something like { "_id" : { "map" : "France" }, "laps" : [ "Jonathan Smith":"2:32:34", "Someone Else":"1:32:29" ], "count" : 2.0 } – someonewithakeyboard Mar 12 at 1:16
  • I don't think mongodb supports key value pairs in an array but you can put an object with driverName and lapTime keys inside laps field. Check the updated answer – cEeNiKc Mar 12 at 2:08
  • the driver section is an empty array, that's with the updated query. – someonewithakeyboard Mar 12 at 9:08

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.