MongoDB Date objects in queries

[vk496]

The idea is:

Be able to use Extended JSON from MongoDB and use date objects

My use case:

I have a test collection with one element:

{
    _id: ObjectId('65dcf9944e30e4c5149a2b27'),
    example_date: ISODate('2024-01-25T20:13:23.813Z')
}

I want to be able to filter by example_date attribute. Right now, both of the following queries return empty results:

{
  "example_date": {
        "$gt": "2021-12-12T12:12:12.123Z"
      }
}

nor

{
  "example_date": {
        "$gt": { "$date": "2021-12-12T12:12:12.123Z" }
      }
}

Since pure JSON is enforced, I can’t pass JS Date objects neither use MongoDB Query Language (MQL) for the queries

I think it would be beneficial to add this because:

If the collection contains 1 million entries, my only solution is query ALL of them and then filter in n8n by date.

Any resources to support this?

Previous discussions:

Query by timestamp in mongodb

Hi guys, I’ve the following query (find) in mongodb trying to search every record after a given date: { "customer.slug": "{{$json["slug"]}}", "status": {"$in": ["captured", "canceled"]}, "created_at": {"$gt": {"$date": "2021-08-01T00:00:00.000Z"}} } Results: if I remove the created_at statement, a lot of records return; if I use as above, no records return; if I use "created_at": {"$gt": "2021-08-01T00:00:00.000Z"} no records return Is that possible to filter by date objects in mongodb thr…

Mongo Node: prefiltering by dates

Yes, as I mentioned, that is saldy not possible from the query box. We would need to abstract the filter functionality and provide an UI. Something where you can set the field, the type and the value.

Are you willing to work on this?

Yes. If somebody could point me to the places in the source code where this is handled incorrectly, I can submit a Merge Request

[antonk1306]

I’m also interested in this feature! I need to filter dates after retrieving all my BD from Mongo when I could just query directly in the petition.

[lehno]

Looking for something like this as well

[Joaqim]

I found a working Aggregate solution using ChatGPT:

Use MongoDB’s $expr to explicitly cast the strings to dates. This approach ensures that the dates are interpreted correctly.

[
  {
    "$match": {
      "$expr": {
        "$and": [
          {
            "$gte": [
              "$example_date",
              {
                "$dateFromString": {
                  "dateString": "2023-05-01T00:00:00.000Z"
                }
              }
            ]
          },
          {
            "$lt": [
              "$example_date",
              {
                "$dateFromString": {
                  "dateString": "2023-06-01T00:00:00.000Z"
                }
              }
            ]
          }
        ]
      }
    }
  }
]

[romulo]

I’m facing the same issue here.

The Joaqim’s solution worked for me, though.

Thank you guys.