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

Working with Data
,
1

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

3

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.

4

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'
},


]
5

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.

6

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

7

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.

8

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

10

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 }
  ]
} }
11

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