Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-34428

$facet aggregation stage does not use index created for the sub pipelines in it and perform COLLSCAN instead.

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 3.6.3
    • Component/s: Aggregation Framework
    • None
    • ALL

      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.

      Unable to find source-code formatter for language: shell. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
      // 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:[]}}}
  
  ]
  }
}
])

            Assignee:
            nick.brewer Nick Brewer
            Reporter:
            srivani.kambhampati@optym.com srivani kambhampati
            Votes:
            1 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated:
              Resolved: