How can I perform this mongoDB lookup aggregation faster?

Question

I want to run the following mongoDB query on Robo 3T, But it takes for ever to bring out an outcome:

db.getCollection('check').aggregate([
{ 
    $match:
    {
        $and:
        [
            {datetime: { "$gt" : new ISODate("2019-07-01 01:00:10.000Z")}},
        ]
    }
},
{
    $lookup:
        {
            from: "workstation",
            localField: "deviceid",
            foreignField: "_id",
            as: "workstation"
        }    
},
{
    $unwind: {path: "$workstation", preserveNullAndEmptyArrays: true}
},    
{
    $lookup:
        {
            from: "server",
            localField: "deviceid",
            foreignField: "_id",
            as: "server"
        }    
},
{
    $unwind: {path: "$server", preserveNullAndEmptyArrays: true}
},  

{
     $lookup: 
        {
            from: "site",
            let: {
                ssiteid : "$server.siteid",
                wsiteid : "$workstation.siteid"
            },
            pipeline: [
                { $match:
                    { $expr: {                            
                        $or: [
                            {$eq : ["$_id","$$ssiteid"]},
                            {$eq : ["$_id","$$wsiteid"]}
                        ]
                        }                                                
                    }                                               
                }],                
            as: "site"
        }      
},
{
    $unwind: {path: "$site", preserveNullAndEmptyArrays: true}
},  

{
     $lookup: 
        {
            from: "client",
            localField: "site.clientid",
            foreignField: "_id",
            as: "client"
        }      
},
{
    $unwind: {path: "$client", preserveNullAndEmptyArrays: true}
},

{ $project: { 
    "_id": 1, 
    "description": 1, 
    "extra": 1,
    "datetime": 1,
    "cname" : "$client.name",    
    "apiKey" : "$client.apiKey",
    "workstation": 1 ,
    "server":1
    }    
},

{ $match: 
    { "client.apiKey":"ae0a4c75230afae756fcfecd3d2838cf"}
},

 {$limit: 30}
])

However, if I remove the last match, then it takes 2 seconds to give the result!!

As the content of the collections, the following is check collection:

{
"_id" : ObjectId("5c1bbcfbfe78c90007af2676"),
"_class" : "dsadsa.ewrwer.werew,
"deviceid" : 943955,
"checkid" : "23303140",
"description" : "fdskfhsdj kfsdjfhskdjf hksdjfhsd kjfs",
"checkstatus" : "testerror",
"datetime" : ISODate("2018-12-04T15:55:00.000Z"),
"smsalerts" : 0,
"emailrecoveryalerts" : 1,
}

and site collection:

{
"_id" : 126581,
"_class" : "dsadsa.ewrwer.werew,
"clientid" : 94011,
"name" : "dsadas, dsadsa",
"connectionOk" : 1,
"primaryRouter" : "",
"secondaryRouter" : "",
"lastUpdate" : ISODate("2018-01-02T13:00:04.713Z"),
"enabled" : false
}

and client :

{
"_id" : 96763,
"_class" : "dsadsa.ewrwer.werew,
"name" : "JOhn Smith",
"viewDashboard" : 0,
"viewWkstsnAssets" : 0,
"dashboardUsername" : "none",
"timezone" : "",
"creationDate" : ISODate("2017-02-09T23:00:00.000Z"),
"serverCount" : 0,
"workstationCount" : 0,
"mobileDeviceCount" : 0,
"deviceCount" : 0,
"apiKey" : "dsaawedsa",
"lastUpdate" : ISODate("1970-03-17T11:00:00.000Z"),
"enabled" : false

}

Is there any way to perform a query like this (or similar ones) faster?

Also, is there any faster way than the GUI-based Robo mongo to access mongoDB?

How can I perform this mongoDB lookup aggregation faster?

Answers (1)

Related Questions