How to find users who do not have any data between two dates in mongodb

I have two collections. The “Users” collection has all the users and “Logs” collection has all the users’ daily logs. I need to find the users who dont have any logs between two dates.

I am able get the users who have data in logs collection between 2 dates but not able to get the users who dont have any data in logs collection between given dates.

Here is the query i tried

db.Users.aggregate([
  {
    $lookup: {
        from: "Logs",
        localField: "_id",
        foreignField: "user_id",
        as: "dd"
      }
   },
  {
      $match:{
     "dd": { "$exists": false }
  ] 
      }
  },
  
]);

</>

I’m struggling with adding the date condition here

We are struggling too since we have no clue about your schema.

Share sample documents from both collections so that we can experiment with your real data.

please find the sample schema below

#users collection 
[
{
_id:1,
name:'user1',
email:'user1@gmail.com'
},
{
_id:2,
name:'user2',
email:'user2@gmail.com'
},
{
_id:3,
name:'user3',
email:'user3@gmail.com'
}

]

#logs collection

[
{
_id:1,
user_id:1,
hours:3,
date:'2023-04-01'
},
{
_id:2,
user_id:1,
hours:5,
date:'2023-04-02'
},
{
_id:3,
user_id:1,
hours:8,
date:'2023-04-03'
},
{
_id:4,
user_id:2,
hours:3,
date:'2023-04-02'
},


]

Simply add a pipeline: in your $lookup that has a $match stage such as

{ "$match" : {
    "date" : { "$gte" : from_date_variable , "$lte" : to_date_variable }
} }

Your current $match will have to test for $size:0 of result field dd rather than the existence.

adding a pipeline would return the users with logs in those dates, but my requirement is to get the users who dont have any logs between those dates

Have you tried my suggestion?

Most likely you did not. The $match inside the $lookup pipeline will find logs of the users between the from and to date. So a user with logs will have a non empty array and a user without logs will have an empty array. Then

which means, that only users with an empty array will be matched. That is users with no logs.

Hey! I missed out the $ sign while adding size. Its working fine now. Thanks!

Now that we have something working a little optimization is in order.

Since you only want the users that have 0 logs, there is no point to $lookup all the logs of a given user. We can stop when we find the first one. To do that we add $limit:1 to the $lookup pipeline: right after the $match. Like:

{ "$lookup" : {
  "from" : "Logs" ,
  /* other fields */
  "pipeline:" : [
    { "$match" : {
      "date" : { "$gte" : from_date_variable , "$lte" : to_date_variable }
    } } ,
    { "$limit" : 1 }
  ]
} }

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.