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.