I have two documents -

const taskSchema = new mongoose.Schema({
    leadId: { type: String, default: '' },
    customer_name: { type: String, default: '' }
})

const leadSchema = new mongoose.Schema({
  Id: { type: String, default: "" },
  associate_status: { type: Boolean, default: false },
})

I want to run a query on tasks such that I get all the leads where associate status is true.

LeadId in task schema is the same as Id in the lead schema.

I tried this -

  const report = await taskModel.aggregate([
   { $lookup: {
      from: 'leads',
      let: { leadId: '$leadId' },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: ['$Id', '$$leadId'],
            },
          },
        },
      ],
      as: 'leaditems',
    },
  }
  { $unwind: '$leaditems' },
  { $match: { leaditems.associate_status: true}}
 ])

But since I have a large number of documents (more than 200k leads and 100k tasks, and these numbers will keep on growing), the query sometimes runs after 9 to 10 seconds and sometimes doesn't runs at all. I am looking for a more optimized approach or any other implementation that will make this work.

Update:

I tried this as well but no improvement -

const report = await taskModel.aggregate([
       { $lookup: {
          from: 'leads',
          let: { leadId: '$leadId' },
          pipeline: [
            {
              $match: {
                $expr: {
                  $and: [
                   {
                     $eq: ['$Id', '$$leadId'],
                   },
                   {
                     $eq: ['$associate_status', true],
                   },
                 ],
               },
              },
            },
          ],
          as: 'leaditems',
        },
      }
     ])

CodePudding user response：

Put $match condition in $lookup pipeline or even try to put it before $lookup.

Set index on both associated id.

Maybe your service has a 10 second database connection constraint, try to make it longer.

CodePudding user response：

To use the index in $exp inside a lookup pipeline you need MongoDB 5. MongoDB 4.4.9 will not use the index and query will be slow.

• create the indexes in leads and Id, its expected to be much faster
  (index helps if it has good selectivity, if select small percentage of the documents, an index that will return 90% of documents can make things worse)(if you add many values on leadId index will be updated also, it can cost, but the benefits most of the times are much more)
• replace with a simple equality $lookup
• Also if you dont really need the $unwind and you prefer your data in array you can use $filter

const report = await taskModel.aggregate([
       { $lookup: {
          from: 'leads',
          localField: "leadId",
          foreignField: "Id",
          as : "leaditems"
       },
       { $unwind: '$leaditems' },
       { $match: { leaditems.associate_status: true}}       
     ])

If possible benchmark it after you do those and send some feedback.