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