MongoDB Query and Array using $exists

Question

I am trying to query a array in which a field does not exist or is in the future. For example I have the following query: {$or:[{"status.date":{$exists:false}}, {"status.date":{$gt: ISODate('2020-08-25T13:47:20+0000')}}]}

and my collection looks similar to this

{
    "status": [{
            "date": "2022-08-25T13:47:20+0000",
            "a": "test1"
        },
        {
            "date": "2010-08-25T13:47:20+0000",
            "a": "test2"
        },
        {
            "a": "test3"
        }
    ]
}
....

The results do not show the above collection because of the "status.date":{$exists:false}} as there is status.date as it exists in status[0] and status[1].

Is there a any other operation I could use to achieve this.

its working perfectly what is the problem ? mongoplayground.net/p/VE4CAiXDzu4 — turivishal
– turivishal, Commented Aug 25, 2020 at 14:06
@turivishal I tried the following query on mongocompass {$or:[{"status.date":{$exists:false}}, {"status.date":{$gt: ISODate('2020-08-25T13:47:20+0000')}}]} but its not getting any results — uniQ
– uniQ, Commented Aug 25, 2020 at 14:20
you should have to check the date format, here you are passing ISODate and in your collection should same date format. — turivishal
– turivishal, Commented Aug 25, 2020 at 14:23
@turivishal actually the date bit is working. its the status.date does not exist that is not working. for example mongoplayground.net/p/Eo6nDoseZ4d should return the object too since status[2] does not have a date. — uniQ
– uniQ, Commented Aug 25, 2020 at 14:29

Gibbs · Accepted Answer · 2020-08-25 16:19:26Z

2

You could do this

Play

Add a unwind stage as its an array.

db.collection.aggregate([
  {
    $unwind: "$status"
  },
  {
    $match: {
      $or: [
        {
          "status.date": {
            $exists: false
          }
        }
      ]
    }
  }
])

To get the entire document, you can use elemMatch

play

db.collection.aggregate([
  {
    $match: {
      "status": {
        $elemMatch: {
          "date": {
            $exists: false
          }
        }
      }
    }
  }
])

edited Aug 25, 2020 at 16:19

answered Aug 25, 2020 at 14:38

Gibbs

23.1k14 gold badges84 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

uniQ Over a year ago

i dont want to unwind. I basically want the following: if date is in the future or does not exist then get the whole collection. not just 1 array.

Gibbs Over a year ago

Whole collection or whole document?

uniQ Over a year ago

sorry, whole document.

uniQ Over a year ago

thank you, the part i was missing was elemMatch. :)

Mallik · Accepted Answer · 2020-08-25 15:22:51Z

I have used in the sample collection a format as shown. it works. can you please execute the code in your environment. You have to use a $unwind command to show the specific array elements you are looking for.

//source data:
> db.test7.find().pretty();
{
        "_id" : ObjectId("5f45266ef3d99fcad8f72e05"),
        "status" : [
                {
                        "date" : "2020-08-25T14:50:39.496Z",
                        "a" : "test1"
                },
                {
                        "date" : "2020-08-29T14:50:39.496Z",
                        "a" : "test2"
                },
                {
                        "date" : "2020-08-01T14:50:39.496Z",
                        "a" : "test3"
                },
                {
                        "a" : "test4"
                }
        ]
}
//you can play around dates for formats eg. today and today1 below, so that date validation check is correct when using $gt.
var today = ISODate();
print("var today: ",today);
var today1 = "2020-08-25T14:50:39.496Z"
print("var today1: ",today1);
db.test7.aggregate([
 {$unwind:"$status"},
 {$match:
    {$or:[
        {"status.date":{"$exists":false}},
        {"status.date":{"$gt":today1}}
         ]
    }
  }
]).pretty();
//output as you needed
{
        "_id" : ObjectId("5f45266ef3d99fcad8f72e05"),
        "status" : {
                "date" : "2020-08-29T14:50:39.496Z",
                "a" : "test2"
        }
}
{
        "_id" : ObjectId("5f45266ef3d99fcad8f72e05"),
        "status" : {
                "a" : "test4"
        }
}
>

Collectives™ on Stack Overflow

MongoDB Query and Array using $exists

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related