Mongodb selecting documents greater than a date

Question

I'm having a hard time getting back just the document and subdocument I want. Given the following example, I want MyArray.data returned where somedate is greater than 2018-07-09. I've seen a number of examples but none of them seem to work for me. We're using v3.4.

I tried

db.getCollection('TestCol').find({"MyArray.data.somedate": {"$gt": Date("2018-07-09")}})

but nothing is returned.

Here's the document I'm testing with

{
"_id" : ObjectId("5b46770ca6476fde4407a8df"),
"MyArray" : [ 
    {
        "data" : {
            "somedate" : "2018-07-10 16:34"
        }
    }, 
    {
        "data" : {
            "somedate" : "2018-07-09 15:01"
        }
    }
  ]
}

Update I also tried:

db.getCollection('TestCol').aggregate([{$match: {"MyArray.data.somedate": {"$gte": Date("2018-07-09")}}}])

If your date is string in the document then you need to use aggregation query here — Ashh, Jul 12 '18 at 03:34
I tried using $match within aggregate and I get 'unknown top level operator: $match' . — jbassking10, Jul 12 '18 at 13:58
Please post the query you have tried... Are you using node or other framework? — Ashh, Jul 12 '18 at 13:59
I got rid of the error but I don't get any results. I updated the code above with the aggregate. — jbassking10, Jul 12 '18 at 18:56

score 1 · Answer 1 · answered Jul 12 '18 at 19:51

Your document doesn't contain a date object, but rather it contains a string. And that string is not formatted as a proper ISODate string or some other string. You'll need to transform the date you're passing to Mongo query to be in the same format as your field in the DB in order to match the dates.

For transformation, you can use date-fns format function as this:

const format = require("date-fns/format")
const dateForQuery = format(new Date("2018-07-09"), 'YYYY-MM-DD HH:mm')
const data = await db.collection.find({"MyArray.data.somedate": {"$gt": dateForQuery}})

On the other hand, you have some bad practice in your code:

You should not save dates as strings in the DB. You should save Date instance directly to Mongo (see this answer).
Your created date depends on time zone of your server (I'm in CET and created date for your example was "2018-07-09 02:00"). So you'll have to pass the time part to your new Date() call. But bear in mind that it's always in your local timezone. If you're always dealing with time in your server's timezone, than it's ok not to pass the time part.

Mongodb selecting documents greater than a date

1 Answers1