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.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Duplicate
    • Affects Version/s: 3.6.3
    • Fix Version/s: None
    • Component/s: Aggregation Framework
    • Labels:
      None
    • Operating System:
      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

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: