Hi,
We have implemented the aggregation in the app. But the aggregation query is taking 20 sec to load.
We have the $match condition $facet stage.
Below is the sample query:
db.used_vehicles.explain(“executionStats”).aggregate([{“$match”:{“status”:“ACTIVE”,“media_exist”:1,“vehicle_type_id”:1,“listing_status”:“APPROVED”}},{“$facet”:{“categoryByState”:[{“$match”:{“used_vehicle_spec.state.state_name”:{“$ne”:null}}},{“$group”:{“_id”:“$used_vehicle_spec.state.state_id”,“state_identifier”:{“$first”:“$used_vehicle_spec.state.state_identifier”},“state_name”:{“$first”:“$used_vehicle_spec.state.state_name”},“state_count”:{“$sum”:1}}},{“$project”:{“_id”:0,“state_id”:“$_id”,“state_identifier”:“$state_identifier”,“state_name”:“$state_name”,“state_count”:“$state_count”}},{“$sort”:{“state_count”:-1,“state_name”:1}}],“categoryByCity”:[{“$match”:{“used_vehicle_spec.city.city_name”:{“$ne”:“”}}},{“$group”:{“_id”:“$used_vehicle_spec.city.city_id”,“city_identifier”:{“$first”:“$used_vehicle_spec.city.city_identifier”},“city_name”:{“$first”:“$used_vehicle_spec.city.city_name”},“state_id”:{“$first”:“$used_vehicle_spec.city.state_id”},“city_count”:{“$sum”:1}}},{“$project”:{“_id”:0,“city_id”:“$_id”,“city_identifier”:“$city_identifier”,“city_name”:“$city_name”,“state_id”:“$state_id”,“city_count”:“$city_count”}},{“$sort”:{“city_count”:-1,“city_name”:1}}],“categoryByBrand”:[{“$match”:{“used_vehicle_spec.city.city_id”:{“$in”:[349]},“brand.brand_name”:{“$ne”:null}}},{“$group”:{“_id”:“$brand.brand_id”,“brand_name”:{“$first”:“$brand.brand_name”},“count”:{“$sum”:1}}},{“$project”:{“_id”:0,“brand_id”:“$_id”,“brand_name”:“$brand_name”,“brand_count”:“$count”}},{“$sort”:{“brand_count”:-1,“brand_name”:1}}],“categoryByModel”:[{“$match”:{“used_vehicle_spec.city.city_id”:{“$in”:[349]},“model.model_name”:{“$ne”:null}}},{“$group”:{“_id”:“$model.model_id”,“brand_id”:{“$first”:“$brand.brand_id”},“model_name”:{“$first”:“$model.model_name”},“count”:{“$sum”:1}}},{“$project”:{“_id”:0,“model_id”:“$_id”,“model_name”:“$model_name”,“brand_id”:“$brand_id”,“model_count”:“$count”}},{“$sort”:{“model_count”:-1,“model_name”:1}}],“categoryByBody”:[{“$match”:{“used_vehicle_spec.city.city_id”:{“$in”:[349]},“variant.body_type.shape_name”:{“$ne”:null}}},{“$group”:{“_id”:“$variant.body_type.shape_id”,“shape_name”:{“$first”:“$variant.body_type.shape_name”},“body_count”:{“$sum”:1}}},{“$project”:{“_id”:0,“body_type_id”:“$_id”,“body_type_name”:“$shape_name”,“body_count”:“$body_count”}},{“$sort”:{“body_count”:-1,“body_type_name”:1}}],“categoryByFuel”:[{“$match”:{“used_vehicle_spec.city.city_id”:{“$in”:[349]},“variant.fuel_type.fuel_name”:{“$ne”:null}}},{“$group”:{“_id”:“$variant.fuel_type.fuel_id”,“fuel_name”:{“$first”:“$variant.fuel_type.fuel_name”},“fuel_count”:{“$sum”:1}}},{“$project”:{“_id”:0,“fuel_type_id”:“$_id”,“fuel_type_name”:“$fuel_name”,“fuel_type_count”:“$fuel_count”}},{“$sort”:{“fuel_type_count”:-1,“fuel_type_name”:1}}],“categoryBytransmission”:[{“$match”:{“used_vehicle_spec.city.city_id”:{“$in”:[349]},“variant.transmission_type”:{“$ne”:“”}}},{“$group”:{“_id”:“$variant.transmission_type”,“transmission_count”:{“$sum”:1}}},{“$project”:{“_id”:0,“transmission_type”:“$_id”,“transmission_count”:“$transmission_count”}},{“$sort”:{“transmission_count”:-1,“transmission_type”:1}}],“categoryBySeller”:[{“$match”:{“used_vehicle_spec.city.city_id”:{“$in”:[349]},“certified_seller_data.seller_type”:{“$ne”:null}}},{“$group”:{“_id”:“$certified_seller_data.seller_type”,“seller_count”:{“$sum”:1}}},{“$project”:{“_id”:0,“seller_type”:“$_id”,“seller_count”:“$seller_count”}},{“$sort”:{“seller_count”:-1,“seller_type”:1}}],“categoryByOwner”:[{“$match”:{“used_vehicle_spec.city.city_id”:{“$in”:[349]},“used_vehicle_spec.no_of_owners”:{“$ne”:null}}},{“$group”:{“_id”:“$used_vehicle_spec.no_of_owners”,“owner_count”:{“$sum”:1}}},{“$project”:{“_id”:0,“no_of_owners”:“$_id”,“owner_count”:“$owner_count”}},{“$sort”:{“owner_count”:-1,“no_of_owners”:1}}],“categoryByRegister”:[{“$match”:{“used_vehicle_spec.city.city_id”:{“$in”:[349]},“used_vehicle_spec.register_type”:{“$ne”:“”}}},{“$group”:{“_id”:“$used_vehicle_spec.register_type”,“register_count”:{“$sum”:1}}},{“$project”:{“_id”:0,“register_type”:“$_id”,“register_count”:“$register_count”}},{“$sort”:{“register_count”:-1,“register_type”:1}}],“categoryByCertified”:[{“$match”:{“used_vehicle_spec.city.city_id”:{“$in”:[349]},“certified”:{“$ne”:null}}},{“$group”:{“_id”:“$certified”,“certified_count”:{“$sum”:1}}},{“$project”:{“_id”:0,“is_certified”:“$_id”,“certified_count”:“$certified_count”}},{“$sort”:{“certified_count”:-1}}],“categoryByPrice”:[{“$match”:{“used_vehicle_spec.city.city_id”:{“$in”:[349]},“price”:{“$ne”:“”}}},{“$bucket”:{“groupBy”:“$price”,“boundaries”:[0,500000,1000000,2500000,5000000,7500000,10000000],“default”:10000000,“output”:{“count”:{“$sum”:1}}}},{“$addFields”:{“upperBound”:{“$arrayElemAt”:[[0,500000,1000000,2500000,5000000,7500000,10000000],{“$sum”:[{“$indexOfArray”:[[0,500000,1000000,2500000,5000000,7500000,10000000],“$_id”]},1]}]}}}],“categoryByYear”:[{“$match”:{“used_vehicle_spec.city.city_id”:{“$in”:[349]},“used_vehicle_spec.mfg_year”:{“$ne”:null}}},{“$bucket”:{“groupBy”:“$used_vehicle_spec.mfg_year”,“boundaries”:[0,2011,2014,2017,2019,2022],“default”:2023,“output”:{“count”:{“$sum”:1}}}},{“$addFields”:{“upperBound”:{“$arrayElemAt”:[[0,2011,2014,2017,2019,2022],{“$sum”:[{“$indexOfArray”:[[0,2011,2014,2017,2019,2022],“$_id”]},1]}]}}}],“categoryByKms”:[{“$match”:{“used_vehicle_spec.city.city_id”:{“$in”:[349]},“used_vehicle_spec.kms_driven”:{“$ne”:null}}},{“$bucket”:{“groupBy”:“$used_vehicle_spec.kms_driven”,“boundaries”:[0,30000,60000,100000],“default”:100000,“output”:{“count”:{“$sum”:1}}}},{“$addFields”:{“upperBound”:{“$arrayElemAt”:[[0,30000,60000,100000],{“$sum”:[{“$indexOfArray”:[[0,30000,60000,100000],“$_id”]},1]}]}}}]}}])