CODEWITHSUNDEEP
node.jsmongoose
proquibas
proquibas

Reputation: 91

Mongoose: can't query array of dates

I have a test document like that:

{
    "_id" : ObjectId("5fb6b0ed9cad6e97cfc24c2d"),
    "dates" : [ 
        {
            "date" : ISODate("2020-01-01T00:00:00.000Z")
        }, 
        {
            "date" : ISODate("2020-02-01T00:00:00.000Z")
        }, 
        {
            "date" : ISODate("2020-03-01T00:00:00.000Z")
        }, 
        {
            "date" : ISODate("2020-04-01T00:00:00.000Z")
        }, 
        {
            "date" : ISODate("2020-05-01T00:00:00.000Z")
        }, 
        {
            "date" : ISODate("2020-06-01T00:00:00.000Z")
        }, 
        {
            "date" : ISODate("2020-07-01T00:00:00.000Z")
        }, 
        {
            "date" : ISODate("2020-08-01T00:00:00.000Z")
        }, 
        {
            "date" : ISODate("2020-09-01T00:00:00.000Z")
        }, 
        {
            "date" : ISODate("2020-10-01T00:00:00.000Z")
        }, 
        {
            "date" : ISODate("2020-11-01T00:00:00.000Z")
        }, 
        {
            "date" : ISODate("2020-12-01T00:00:00.000Z")
        }
    ]
}

Now I want to retrieve only dates $gt: 2020-14-01T00:00:00.000Z. I tried a lot of combinations but none of them worked for me.

This is one of the queries I tried (taken from Mongodb docs):

db.getCollection('things').find({_id: ObjectId("5fb6b0ed9cad6e97cfc24c2d"), "dates.date": { $gt: new Date("2020-04-01T00:00:00.000Z")} } )

But it return all the document, not the gt... i tried using new Date and new ISODate too, but same effect.

Thank you.

Upvotes: 0

Views: 294

Answers (1)

J.F.
J.F.

Reputation: 15225

First of all, according to mongo documentation dates are in the format "<YYYY-mm-dd>".
Also more formats are allowed, but If you try to use 2020-14-01 as date it will fail (unless you convert string to date with an specific format) because month is 14.

But, answering the question, you need a query like this:

EDITED

db.collection.aggregate({
  "$match": {
    "_id": ObjectId("5fb6b0ed9cad6e97cfc24c2d"),
    
  }
},
{
  "$project": {
    "dates": {
      "$filter": {
        "input": "$dates",
        "as": "item",
        "cond": {
          "$gt": [
            "$$item.date",
            ISODate("2020-01-14T00:00:00.000Z")
          ]
        }
      }
    }
  }
})

First $match by _id to get only the document you want. And then using $project to create the fields you want to get. You can filter in the array those values whose field date is greater than your date using $filter and $gt

Note that I've used 2020-01-14 to avoid errors.

Example here.
Also another example using $dateFromString in this query.

Edit: You can use $dateFromString and specify a format. Check this example

Upvotes: 1

Related Questions