Details
-
Bug
-
Resolution: Duplicate
-
Major - P3
-
None
-
3.6.3
-
None
-
ALL
Description
i have query like below to implement full outer join using $facet aggregation. If i run them as separate queries, they perform fine and use index i have but if i put in $facet it does not use the index but perform COLLSCAN.
// Some comments here
|
db.servicestats.aggregate([
|
{$facet: {output1:[
|
{$match:{$and :[ {'scenarioId':0}
|
]}},
|
{$lookup: { from:'servicestats',
|
let: {ocode:'$origin.code',dcode:'$destination.code',stype:'$serviceType'},
|
pipeline:[
|
{$match: {
|
$expr: { $and:
|
[
|
{ $eq: [ "$scenarioId", 1 ] },
|
{ $eq: [ "$origin.code", "$$ocode" ] },
|
{ $eq: [ "$destination.code", "$$dcode" ] },
|
{ $eq: [ "$serviceType", "$$stype" ] },
|
]
|
}
|
|
}
|
},
|
{$project: {_id:0, comp :{compavgmiles :'$currentOutput.avgMiles'}}},
|
{ $replaceRoot: { newRoot: "$comp" } }
|
],
|
as : "compoutputs"
|
}},
|
{
|
$replaceRoot: {
|
newRoot: {
|
$mergeObjects:[
|
{
|
$arrayElemAt: [
|
"$$ROOT.compoutputs",
|
0
|
]
|
},
|
{
|
origin: "$$ROOT.origin",
|
destination: "$$ROOT.destination",
|
serviceType: "$$ROOT.serviceType",
|
baseavgmiles: "$$ROOT.currentOutput.avgMiles",
|
output: '$$ROOT'
|
}
|
]
|
}
|
}
|
}
|
],
|
output2:[
|
{$match:{$and :[ {'scenarioId':1}
|
]}},
|
{$lookup: { from:'servicestats',
|
let: {ocode:'$origin.code',dcode:'$destination.code',stype:'$serviceType'},
|
pipeline:[
|
{$match: {
|
$expr: { $and:
|
[
|
{ $eq: [ "$scenarioId", 0 ] },
|
{ $eq: [ "$origin.code", "$$ocode" ] },
|
{ $eq: [ "$destination.code", "$$dcode" ] },
|
{ $eq: [ "$serviceType", "$$stype" ] },
|
]
|
}
|
|
}
|
},
|
{$project: {_id:0, comp :{compavgmiles :'$currentOutput.avgMiles'}}},
|
{ $replaceRoot: { newRoot: "$comp" } }
|
],
|
as : "compoutputs"
|
}},
|
//{
|
// $replaceRoot: {
|
// newRoot: {
|
// $mergeObjects:[
|
// {
|
// $arrayElemAt: [
|
// "$$ROOT.compoutputs",
|
// 0
|
// ]
|
// },
|
// {
|
// origin: "$$ROOT.origin",
|
// destination: "$$ROOT.destination",
|
// serviceType: "$$ROOT.serviceType",
|
// baseavgmiles: "$$ROOT.currentOutput.avgMiles",
|
// output: '$$ROOT'
|
// }
|
// ]
|
// }
|
// }
|
// },
|
{$match :{'compoutputs':{$eq:[]}}}
|
|
]
|
}
|
}
|
])
|
|
Attachments
Issue Links
- related to
-
SERVER-30474 leading $facet with each facet beginning with $match should add $match before $facet
-
- Backlog
-