Aggregate lookups with subarray

[turivishal (Vishal Turi)]

@venkata_sreekanth_bhagavatula

It does generate the result required but it is very slow. The in operator is doing a collection scan of 7 million documents against the backbone array which has like 5 elements

You can improve your query by creating an indexs on match properties, like on structureGroupIdentifier,

Second, the $expr operator can use indexes on properties if you are using MongoDB 5+ version,

If you are using MongoDB’s <5 lower versions then you have to use $lookup with localField and foreignField syntax to support the index. and do other pipeline operations outside $lookup or do it after the query on the front-end side.

For more clearification you need to post explain() result of your query,