Problem on `Collection Index` of `Mongo Lookup`

help
1

For example I have 2 Collection and 1000 records

1- Employee = {
  _id, // index
  name, // index
  gender,
  telephone,
  addId, // lookup to `Address`
}
---
2- Address = {
  _id, // index
  name, // index
}

And then I query

Employee.aggregate([
        {
          $lookup: {
            from: 'address',
            localField: 'addId',
            foreignField: '_id',
            as: 'addDoc',
          },
        },
        {
          $unwind: {
            path: '$addDoc',
            preserveNullAndEmptyArrays: true,
          },
        },
  {
    $match: { 'addDoc.name': 'Battambang'}, // Index don't work
  }
])

Result: Return 10 of 1000 (explain)???
Expect: Return 10 of 10 (Bc address name is Index )

2

The use of indexes in aggregations is not obvious. However, they will only be used if $match is at the top of the pipeline. Computed values from within the pipeline cannot use indexes. In your example, addDoc is computed from $unwind and so cannot use an index.

Maybe you could rewrite the aggregation to run against Address, where you could $match on name at the top of the pipeline and $lookup against Employee if there is a corresponding foreign key reference back to Employee.

There’s also a lot more which happens before a pipeline is executed, to help optimise the throughput. Check the MongoDB Aggregation Pipeline Optimisation doc for more detail.

3

thanks for your reply.
If I use $match on all collection in aggregate like this

Employee.aggregate([
    {
        $match: { .name: 'Theara'}, // Index work on Employee
     },
        {
          $lookup: {
            from: 'address',
            localField: 'addId',
            foreignField: '_id',
            as: 'addDoc',
          },
        },
        {
          $unwind: {
            path: '$addDoc',
            preserveNullAndEmptyArrays: true,
          },
        },
  {
    $match: { 'addDoc.name': 'Battambang'}, // Index don't work
  }
])

Excuse me, have any example to solve this?

4

After an array is unwound, it no longer exists as a named object. You should use

{
  $match: { 'name': 'Battambang'},
}

But the index still won’t work.

5

Thanks again, It mean that Index still don't work after lookup