1

I have two collection Project and Checkins

Project

{"_id": 1, "projectName": "Project A"}
{"_id": 2, "projectName": "Project B"}

Checkins

{ "_id": 1,
  "projectId":1, 
  "checkInType":"abc", 
  "time":"2017-09-26T22:08:30.160Z"
}
{ "_id": 2,
  "projectId":2, 
  "checkInType":"pqr", 
  "time":"2017-09-25T22:08:30.160Z"
}
{ "_id": 3,
  "projectId":2, 
  "checkInType":"abc", 
  "time":"2017-09-20T22:08:30.160Z"
}
{ "_id": 3,
  "projectId":1, 
  "checkInType":"abc", 
  "time":"2017-09-18T28:08:30.160Z"
}

I would like to get all checkins with respective project name order by time descending

i.e

{
   "projectName":"Project A",
    "Checkins":{
       "checkInType":"abc",
       "time":"2017-09-26T22:08:30.160Z"       
     }
}
{
   "projectName":"Project B",
    "Checkins":{
       "checkInType":"pqr",
       "time":"2017-09-25T22:08:30.160Z"       
     }
}
{
   "projectName":"Project B",
    "Checkins":{
       "checkInType":"abc",
       "time":"2017-09-20T22:08:30.160Z"       
     }
}
{
   "projectName":"Project A",
    "Checkins":{
       "checkInType":"abc",
       "time":"2017-09-18T22:08:30.160Z"       
     }
}

its working on test database with or without sort by time, but failed to get data with sort on real big database

here s mongo query

db.getCollection('project').aggregate([
    {$lookup: {from: "checkins", localField: "_id", foreignField: "projectId", as: "checkins"}},
    {$unwind: { path: "$checkins", preserveNullAndEmptyArrays: true }},
    {$sort: {"checkins.time": -1}},
    {$group: {"_id": "$_id", "projectName": {$first: "$projectName"}, "checkins": {$first: "$checkins"}}},
    {$project: {"_id": 1, "projectName": 1, "checkins": 1}}
])
bruno
  • 32,421
  • 7
  • 25
  • 37
Avinash
  • 13
  • 1
  • 4
  • 1
    Where's the code? You say there is an issue, but I see no code in the question. There are several ways to do sort the `$lookup` result. See the "sub-pipeline" example on the [second link](https://stackoverflow.com/questions/22932364/mongodb-group-values-by-multiple-fields/22935461#22935461) for what should be the more efficient approach – Neil Lunn Mar 26 '19 at 09:56
  • here is the mongod query db.getCollection('project').aggregate([ {$lookup: {from: "checkins", localField: "_id", foreignField: "projectId", as: "checkins"}}, {$unwind: { path: "$checkins", preserveNullAndEmptyArrays: true }}, {$sort: {"checkins.time": -1}}, {$group: {"_id": "$_id", "projectName": {$first: "$projectName"}, "checkins": {$first: "$checkins"}}}, {$project: {"_id": 1, "projectName": 1, "checkins": 1}} ]) – Avinash Mar 26 '19 at 10:21

0 Answers0