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',
},
}
])
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.
$lookup$unwind and you prefer your data in array you can use $filterconst 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.
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.