Aggregation $lookup with $let is not working

Question

I have a collection TblStudent in mongodb like

       {
      "_id": ObjectId("5baa85041d7859f40d000029"),
       "Name": "John Doe",
       "RollNo": 12,
       "Class": "Ist"
        ....
       }

I have another collection TblRoute like

   {
   "_id": ObjectId("5baa818d1d78594010000029"),
   "Name": "New york City",
   "StopDetails": [
   {
    "StopId": "abc777",
    "Name": "Block no 3"
   },
   {
   "StopId": "abc888",
   "Name": "Block no 4"
   }
 ],
"NumberOfSeats": "10",
"StudentDetails": [
 {
   "StudentId": ObjectId("5baa85041d7859f40d000029"),
   "VehicleId": "7756"
  },
  {
   "StudentId": ObjectId("5baa85f61d7859401000002a"),
   "VehicleId": "7676"
 }
 ]
}

I am using mongodb 3.6 platform. I am using below lines of code

       $query = ['_id' => new MongoDB\BSON\ObjectID($this->id)];
    $cursor = $this->db->TblRoute->aggregate([
    ['$match' => $query],
    [
    '$lookup' =>
     [
       'from' => "TblStudent",
        'let' => ['StudentId' => '$StudentDetails.StudentId'],
        'pipeline' => [
          [ '$match' =>
             ['$expr' =>        
                ['$eq' => ['$StudentId',  '$$StudentId' ] ]  
             ]
          ],
           [ '$project' => ['Name' => 1, 'RollNo' => 1 ] ]
       ],
       'as' => "StudentDetails.StudentData"
     ]                   
    ] 
  ]);

I have been trying to fetch data from another collection but with certain fields only. I am trying to fetch student Name and RollNo fields from TblStudent inside TblRoute in order to make document array light weight. Normally the $lookup stage fetches all the fields from another collection.

I am trying the above code. It throws error message

"StudentDetails.StudentData" is coming empty 'StudentDetails' => MongoDB\Model\BSONDocument::__set_state(array( 'StudentData' => MongoDB\Model\BSONArray::__set_state(array( )), )), 

but I think that the code is not written correctly. The actual method might be different. Please help me in sorting out the problem.

I want the output to be something like

{
"_id": ObjectId("5baa818d1d78594010000029"),
"Name": "New york City",
"StopDetails": [
     .....
  ],
   "StudentDetails": [
   {
   "StudentId": ObjectId("5baa85041d7859f40d000029"),
   "VehicleId": "7756",
   "StudentData": [
      "Name": ..
      "RollNo":...
    ]
    },
    {
    "StudentId": ObjectId("5baa85f61d7859401000002a"),
    "VehicleId": "7676",
     "StudentData": [
      "Name": ..
      "RollNo":...
    ]
   }
   ]
  }

Menton

What is the error message ? – Dorothi

Fatal error: Uncaught exception 'MongoDB\Driver\Exception\RuntimeException' with message ''StudentId' starts with an invalid character for a user variable name' in – Menton

Okay try [ '$lookup' => [ 'from' => "TblStudent", 'let' => ['studentid' => '$StudentDetails.StudentId'], 'pipeline' => [ [ '$match' => ['$expr' => ['$in' => ['$StudentId', '$$studentid' ] ] ] ], [ '$project' => ['Name' => 1, 'RollNo' => 1 ] ] ], 'as' => "StudentDetails.StudentData" ]. From the docs User variable names must begin with a lowercase ascii letter [a-z] or a non-ascii character – Dorothi

Now that error message disappeared but "StudentDetails.StudentData" is coming empty 'StudentDetails' => MongoDB\Model\BSONDocument::__set_state(array( 'StudentData' => MongoDB\Model\BSONArray::__set_state(array( )), )), – Menton

did you use $in instead of $eq in your $lookup stage ? – Dorothi

Also please note that previously created StudentDetails embbeded document are overwritten with empty array due to StudentDetails.StudentData line – Menton

i have used $in – Menton

Sorry. I don't see anything wrong with the query. Can you try running query in mongo shell ? – Dorothi

Please tell me that is the above written code right method to achieve the goal, – Menton

Let us continue this discussion in chat. – Dorothi

Answer 1

Use the below aggregation.

Note from the docs

User variable names must begin with a lowercase ascii letter [a-z] or a non-ascii character.

So change the $let variable to studentid and also fixed other issues in code.

db.TblRoute.aggregate([
  {"$match":ObjectId("5baa818d1d78594010000029")},
  {"$lookup":{
    "from":"TblStudent",
    "let":{"studentid":"$StudentDetails.StudentId"},
    "pipeline":[
      {"$match":{"$expr":{"$in":["$_id","$$studentid"]}}},
      {"$project":{"Name":1,"RollNo":1}}
    ],
    "as":"StudentDetails.StudentData"
  }}
])

Dorothi

Great solution..... thanks... but it is overwriting the previous StudentDetails. Is there any other method to avoid this overwriting... – Menton

Thank you! Great solution. Did my job. – Guildsman

This doesn't seem to be working, has syntax changed in new version of Mongo? – Wharfinger

what if the checking is not the StudentId, instead want to use StudentData to do the checking? – Unanimous