Mongo lookup Query

Hello Team,

I need help in mongo lookup query where while i using mongo lookup between 2 collections. I am getting result fine from first collection where i need active count from second collection as well. I am not finding any solution to adjust this count in query. I am using group by with first collection and now required count from second collection.

Please provide sample documents from both collections and the aggregation you are using.

Welcome @Varinder_Patwal on MongoDB Official Community platform.
It is requested you to always share a sample document, your query, expected output/issue so that if anyone want to do some operation he/she will use your sample document. It’s a good way to ask questions.

Hi @Nabeel_Raza , Thanks for your suggestion. Will do the same next time.

Attached is the query and expected output result.

Hoping for help.

share it in text form not in pictorial.

Hi Team - Below are the collections and using query.

Collection 1 - userlogs

db.userlogs.findOne()
{
        "_id" : ObjectId("5e6f9850bf1f7f22659c2051"),
        "userLogInTime" : ISODate("2020-03-16T15:16:32.877Z"),
        "userId" : ObjectId("5e6f9832bf1f7f22659c2050"),
        "userLogOutTime" : null,
        "createdAt" : ISODate("2020-03-16T15:16:32.878Z"),
        "updatedAt" : ISODate("2020-03-16T15:16:32.878Z"),
        "__v" : 0
}

collection 2 - users

db.users.findOne()
{
        "_id" : ObjectId("5e7508b752eeffdd9c604fca"),
        "username" : "130123",
        "createdAt" : ISODate("2020-03-20T18:17:27.646Z"),
        "createdBy" : null,
        "email" : "abc@gmail.com",
        "isActive" : true,
       "updatedAt" : ISODate("2020-06-08T10:12:21.557Z"),
}

Using query -

db.userlogs.aggregate([  {    '$match': {'userId': new ObjectId('5f27c8967655fc302b0a842d'), 'createdAt': {  '$gte': new Date('Mon, 01 Jun 2020 00:00:00 GMT'), 
'$lte': new Date('Mon, 28 Sep 2020 00:00:00 GMT') }, '$or': [  {    'userLogOutTime': {'$ne': null    }  },   {    'lastHeartBeat': {'$ne': null    }  }]    }  }, 
{    '$group': {'_id': {  '$dateToString': {    'format': '%Y-%m-%d',     'date': '$createdAt'  }},
'totalLoggedTime': {   '$sum': {    '$cond': [{  'userLogOutTime': null}, {  '$subtract': [    '$lastHeartBeat', '$userLogInTime'  ]},
{  '$subtract': [    '$userLogOutTime', '$userLogInTime'  ]}    ]  }}, 'userId': {  '$first': '$userId'
}, 'userLogInTime': {  '$first': '$userLogInTime'}    }  },
{    '$lookup': {'from': 'users', 
'let': {  'user': '$userId'},
 'pipeline': [  {    '$match': {'$expr': {   '$eq': [  '$_id', '$$user'  ]}, "processName":{$eq:"Samsung Chat"}, "isActive" : true	}  }
 ] ,  'as': 'userdetails' }},
 {$group :{_id :"Totalloginseconds"  , "totalloginsec": { "$sum": "$totalLoggedTime" } 	}},
 {'$project': { '_id': 0, 'totalloginsec': 1 ,'activeHeadCounts':{"$sum":'$userdetails.userId'}  }  },  
{'$sort': {'date': -1    }  }]);

I need count from lookup collection users in result along with total login.

Will be thankfull for this.

I executed your code and found this:

@Nabeel_Raza I shared you sample collections format. This will not give you result. I was trying to take help from someone that can fulfill my requirement.
My question was how to get active headcount from users collections in $project as used “activeHeadCount:{$sum:”$userdetails._userId"}". Need to confirm syntax and solution.

@steevej Please help as sample data with collection and query shared.

Yes, the methodology will be the same filter out the documents, then group them and then get the count form that. But make sure that you’ve a projection list where you can add new (additional field).

Here is the link of same type of question on stack overflow: Active and Total User Count Mongodb - Stack Overflow

db.user.aggregate([
    { "$match": { 'phoneInfo.verifiedFlag': true} },
    { 
        "$group": {
            "_id": { 
                "day": { 
                    "$dateToString": { 
                        "format": "%Y-%m-%d", 
                        "date": "$createdOn" 
                    } 
                },
                "status": { "$toLower": "$status" }
            },
            "count": { "$sum": 1 }
        }
    },
    { 
        "$group": {
            "_id": "$_id.day",
            "counts": {
                "$push": {
                    "status": "$_id.status",
                    "count": "$count"
                }
            }
        }
    },
    { "$sort": { "_id": 1 } }
])