CODEWITHSUNDEEP
arraysmongodbmongodb-query
Bahram Hasanov
Bahram Hasanov

Reputation: 35

mongodb - ignore document if one of elements in array matches query

I have a user object in users colelction like below:

{
  "username": "bahramhasanov",
  "sessions": [
    {
      "time": "2018-12-10 12:30"
    },
    {
      "time": "2018-10-11 13:30"
    }
  ]}

Now I want to get users who does not have any session between '2018-10-01' and '2018-10-15'. When I query with $elemMatch and $not I get bahramhasanov because he has a session out of this date range.

I used this query:

db.users.find({sessions:{$elemMatch: {"time": {$not:{$gt: ISODate('2018-10-01'),$lt: ISODate('2018-10-15')}}}}})

Upvotes: 2

Views: 464

Answers (2)

Ashh
Ashh

Reputation: 46461

You have to use $not query operator in order to oppose the whole $elemMatch operator not only with the $lte and the $gte

And also the time is in String not in ISODate format

db.collection.find({
  "sessions": {
    "$not": {
      "$elemMatch": {
        "time": {
          "$gt": ISO("2018-12-17T20:00:00.000+0000"),
          "$lt": ISO("2018-12-17T20:00:00.000+0000")
        }
      }
    }
  }
})

Upvotes: 1

Lajos Arpad
Lajos Arpad

Reputation: 76591

To query users who do not have any session between a date range, you will need to query users who have sessions outside the date range and then query users who are not among them. In your query you get users who have sessions inside the date range, which will indeed return users who have sessions inside the date range and outside the date range and will not return users inside the date range. In terms of sets, let us consider

O = "the users having sessions outside the date range"

I = "the users having sessions inside the date range"

A = "all users"

O ∩ I = "the users having sessions inside and outside the date range"

O ∪ I = "the users having sessions inside or outside the date range"

Now, you need

A \ O and in order to do it, you will need to find O and then filter its items out from A.

Upvotes: 1

Related Questions