Index not working with $project

[steevej (Steeve Juneau)]

@Mohsin_Rizvi

One more thing I have witnessed is that if I modify my $match stage by replacing $elemMatch with a filter directly on the field.

I think that this is really the key of the issue. When you do

@Mohsin_Rizvi

serviceableMarkets: {
    $elemMatch: {
        providerCode: "GHI"
    }
}

You are querying the array serviceableMarkets, while with

@Mohsin_Rizvi

{
  "serviceableMarkets.providerCode": "GHI"
}

You are querying the indexed field serviceableMarkets.providerCode.

While the server could try to analyze the inside of $elemMatch to see if all the queried fields are indexed, I think that it might be too complex or expensive to do just to accommodate some edge cases like yours.

I think that once you know $elemMatch might force a fetch before deciding if documents match or not, you can decide not to use it when querying a single indexed array field.