Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Aggregation $lookup with $let is not working

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":...
    ]
   }
   ]
  }
like image 856
Nida Amin Avatar asked Oct 08 '18 19:10

Nida Amin


People also ask

Can we join 2 collections in MongoDB?

For performing MongoDB Join two collections, you must use the $lookup operator. It is defined as a stage that executes a left outer join with another collection and aids in filtering data from joined documents.

How do you populate with aggregate?

With the latest version of mongoose (mongoose >= 3.6), you can but it requires a second query, and using populate differently. After your aggregation, do this: Patients. populate(result, {path: "patient"}, callback);

What is $lookup in MongoDB?

$lookup performs an equality match on the localField to the foreignField from the documents of the from collection. If an input document does not contain the localField , the $lookup treats the field as having a value of null for matching purposes.

How aggregation works in MongoDB?

In MongoDB, aggregation operations process the data records/documents and return computed results. It collects values from various documents and groups them together and then performs different types of operations on that grouped data like sum, average, minimum, maximum, etc to return a computed result.


1 Answers

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"
  }}
])
like image 65
s7vr Avatar answered Nov 15 '22 22:11

s7vr