{
"collection": "dispatchrequests",
"aggregate": [
{
"$match": {
"source_city_name": "{{Source}}",
"destination_city_name": "{{destination}}",
"start_date": {
"$gte": "{{doj-start}}",
"$lte": "{{doj-end}}"
},
"booking_status": "TRAVELLED"
}
},
{
"$group": {
"_id": {
"source_city_name": "$source_city_name",
"destination_city_name": "$destination_city_name",
"start_date": "$start_date"
},
"count_dispatchrequests": {
"$sum": 1
}
}
},
{
"$project": {
"_id": 0,
"source_city_name": "$_id.source_city_name",
"destination_city_name": "$_id.destination_city_name",
"start_date": "$_id.start_date",
"count_dispatchrequests": 1
}
},
{
"$lookup": {
"from": "competitivepricings",
"let": {
"source": "$source_city_name",
"destination": "$destination_city_name",
"date": "$start_date"
},
"pipeline": [
{
"$match": {
"$expr": {
"$and": [
{ "$eq": ["$source_city_name", "$$source"] },
{ "$eq": ["$destination_city_name", "$$destination"] },
{ "$eq": ["$start_date", "$$date"] },
{ "$eq": ["$funnel_type", "NORMAL"] }
]
}
}
}
],
"as": "competitive_data"
}
},
{
"$unwind": {
"path": "$competitive_data",
"preserveNullAndEmptyArrays": true
}
},
{
"$group": {
"_id": "$start_date",
"count_competitivepricings": {
"$sum": 1
},
"count_dispatchrequests": {
"$first": "$count_dispatchrequests"
}
}
},
{
"$project": {
"_id": 0,
"source_city_name": "$_id.source_city_name",
"destination_city_name": "$_id.destination_city_name",
"start_date": "$_id",
"count_competitivepricings": 1,
"count_dispatchrequests": 1,
"conversionRatio": {
"$cond": [
{ "$eq": ["$count_competitivepricings", null] },
null,
{ "$divide": ["$count_competitivepricings", "$count_dispatchrequests"] }
]
}
}
}
]
}
One of the indexes in the database is source_city_name_destination_city_name_updatedAt. Can this be integrated in order to optimise this query as it is taking too long to run or is there any other method to optimise this?
This operation is designed to get the ratio of count_competitivepricings and count_dispatchrequests using two collections dispatchrequests and competitivepricings.