I have a Student Table in mongo db like
{
"_id": ObjectId("5baa85f61d7859401000002a"),
"Name": "Bella Dave",
"RollNo": 12,
"Class": "Ist",
"TransportDetails": [
{
"RouteId": ObjectId("5baa93a21d7859401000002b"),
"StopId": "abc123",
"Status": "Inactive"
},
{
"RouteId": ObjectId("5baa818d1d78594010000029"),
"StopId": "abc456",
"Status": "Active"
}
]
}
I have Route Table like
{
"Name": "New york City",
"StopDetails": [
{
"StopId": "abc123",
"Name": "Block no 1"
},
{
"StopId": "abc567",
"Name": "Block no 2"
}
]
I have written below aggregation query like
$cursor = $this->db->TblStudent->aggregate([
[
'$addFields' => [
'ActiveRouteId' => [
'$map' => [
'input' => '$TransportDetails',
'as' => 'item',
'in' => [
'$cond' => [
['$eq' => ['$$item.Status', "Active"]],
'$$item.RouteId',
false
]
]
]
]
]
],
[
'$addFields' => [
'ActiveStopId' => [
'$map' => [
'input' => '$TransportDetails',
'as' => 'item',
'in' => [
'$cond' => [
['$eq' => ['$$item.Status', "Active"]],
'$$item.StopId',
false
]
]
]
]
]
],
array(
'$lookup' => array(
'from' => 'TblRoute',
'localField' => 'ActiveRouteId',
'foreignField' => '_id',
'as' => 'RouteDetails'
)
),
array(
'$lookup' => array(
'from' => 'TblRoute',
'localField' => 'ActiveStopId',
'foreignField' => 'StopDetails.StopId',
'as' => 'StopDetails'
)
),
])->toArray();
return $cursor;
Basically, I have to get active Route and Stop information along with student data. So, I successfully fetched ActiveRouteId and ActiveStopId using $addFields and $map operators. Based on ActiveRouteId, I am doing $lookup for fetching active route information. I am successfully getting that in "RouteDetails" embedded document. Now I issue is in the line
array(
'$lookup' => array(
'from' => 'TblRoute',
'localField' => 'ActiveStopId',
'foreignField' => 'StopDetails.StopId',
'as' => 'StopDetails'
)
),
This lookup is not fetching anything. Please help!!!
Is it possible to find route and stop information together. I mean in route table there are many other embedded documents, if it is possible to fetch the required embedded document like
RouteDetails: [
"Name": "New york City",
"StopDetails": [
{
"StopId": "abc123",
"Name": "Block no 1"
}
]
You can try below aggregation
TblStudent.aggregate([
[ "$addFields"=> [
"TransportDetails"=> [
"$cond"=> [
"if"=> [
"$ne"=> [ [ "$type"=> "$TransportDetails" ], "array" ]
],
"then"=> [],
"else"=> "$TransportDetails"
]
]
]],
[ "$addFields"=> [
"ActiveRouteId"=> [
"$filter"=> [
"input"=> "$TransportDetails",
"as"=> "item",
"cond"=> [ "$eq"=> ["$$item.Status", "Active"] ]
]
]
]],
[ "$lookup"=> [
"from"=> "TblRoute",
"let"=> [ "activeRouteId"=> "$ActiveRouteId.RouteId" ],
"pipeline"=> [
[ "$match"=> [ "$expr"=> [ "$in"=> ["$_id", "$$activeRouteId"] ]]]
],
"as"=> "RouteDetails"
]],
[ "$lookup"=> [
"from"=> "TblRoute",
"let"=> [ "activeStopId"=> "$ActiveRouteId.StopId" ],
"pipeline"=> [
[ "$unwind"=> "$StopDetails" ],
[ "$match"=> [ "$expr"=> [ "$in"=> ["$StopDetails.StopId", "$$activeStopId"] ]]],
],
"as"=> "StopDetails"
]]
])
Similar to javascript
TblStudent.aggregate([
{ "$addFields": {
"TransportDetails": {
"$cond": {
"if": {
"$ne": [ { "$type": "$TransportDetails" }, "array" ]
},
"then": [],
"else": "$TransportDetails"
}
}
}},
{ "$addFields": {
"ActiveRouteId": {
"$filter": {
"input": "$TransportDetails",
"as": "item",
"cond": { "$eq": ["$$item.Status", "Active"] }
}
}
}},
{ "$lookup": {
"from": "TblRoute",
"let": { "activeRouteId": "$ActiveRouteId.RouteId" },
"pipeline": [
{ "$match": { "$expr": { "$in": ["$_id", "$$activeRouteId"] }}}
],
"as": "RouteDetails"
}},
{ "$lookup": {
"from": "TblRoute",
"let": { "activeStopId": "$ActiveRouteId.StopId" },
"pipeline": [
{ "$unwind": "$StopDetails" },
{ "$match": { "$expr": { "$in": ["$StopDetails.StopId", "$$activeStopId"] }}},
],
"as": "StopDetails"
}}
])
Gives me following output
/* 1 */
{
"_id" : ObjectId("5baa85f61d7859401000002a"),
"Name" : "Bella Dave",
"RollNo" : 12,
"Class" : "Ist",
"TransportDetails" : [
{
"RouteId" : ObjectId("5baa93a21d7859401000002b"),
"StopId" : "abc123",
"Status" : "Inactive"
},
{
"RouteId" : ObjectId("5baa818d1d78594010000029"),
"StopId" : "abc456",
"Status" : "Active"
}
],
"ActiveRouteId" : [
{
"RouteId" : ObjectId("5baa818d1d78594010000029"),
"StopId" : "abc456",
"Status" : "Active"
}
],
"routeDetails" : [
{
"_id" : ObjectId("5baa818d1d78594010000029"),
"Name" : "New york City",
"StopDetails" : [
{
"StopId" : "abc123",
"Name" : "Block no 1"
},
{
"StopId" : "abc567",
"Name" : "Block no 2"
}
]
}
],
"StopDetails" : [
{
"_id" : ObjectId("5baa93a21d7859401000002b"),
"Name" : "New york City",
"StopDetails" : {
"StopId" : "abc456",
"Name" : "Block no 2"
}
}
]
}