8

I'm trying to perform a date range query on the _id field using compass. I've tried what I found here with the following filter:

{_id: { $gte: ObjectId.fromDate(new Date('2019-01-01')) } }

What am I missing? I'd like to get a list of all documents from some date forward (in this example from 1 Jan 2019 to present). Unfortunately there isn't a timestamp in the document fields so I need to extract it from the object id.

geco17
  • 5,152
  • 3
  • 21
  • 38

2 Answers2

13

You need to pass a date object to the ObjectId.fromDate, not a string. Try this:

ObjectId.fromDate(new Date('2019-01-01'))

This function works only in the shell and doesn't exist in the drivers.

EDIT after comments:

Here is a solution that works in Compass as well:

{ 
    $expr: { 
        $gte: [
            {"$toDate":"$_id"}, 
            ISODate("2021-01-01T00:00:00.000Z") 
        ]
    } 
}

Keep in mind, however, that it requires a version of mongo of 4.0+. You can checkout the docs here.

Also, checkout this related topic: Can I query MongoDB ObjectId by date?

It is not about Compass, but it provides solutions for generating the ObjectIds from a date without being dependent on ObjectId.fromDate().

zhulien
  • 5,145
  • 3
  • 22
  • 36
  • Hi, I updated my question, I didn't realize I had written it wrong. I'm already doing the query with `new Date(...)` but if I'm reading you correctly, this can't be done within compass and has to be from the shell? – geco17 Feb 15 '21 at 14:17
  • 1
    It's actually a peculiar case with Compass. The request works fine in `Robo 3T`, an alternative of Mongo Compass. And Compass should actually use an embedded MongoDB Shell but the request neither fails, nor works. I'm leaving a related topic for generating the ObjectIds by date as an edit to my answer. – zhulien Feb 15 '21 at 14:34
  • @WilliamBurnham The solution in the edit works for both the shell and the drivers, including Compass and is entirely self-sufficient as it doesn't involve custom functions. – zhulien Feb 15 '21 at 14:52
4

I found a solution, maybe not the best but it does the job. Hopefully this can help someone with the same problem if there isn't a cleaner solution.

I converted the date I needed to an ObjectId outside of compass online here. Then I wrote the query with that ObjectId:

{_id: { $gte: ObjectId(' object id here ') } }

As suggested in the comments, see this related topic. It's not specific to Compass but it provides a solution for generating ObjectIds from a date without being dependent on ObjectId.fromDate().

geco17
  • 5,152
  • 3
  • 21
  • 38