Mongo Node: prefiltering by dates

Try enclosing the $gte in quotes:

{"active": true, "createdAt" : { "$gte" : new Date("2021-01-12T20:15:31Z") }}
1 Like

Hi @mluhta,

I have added this query in a Find Operation and I get

ERROR: Unexpected token e in JSON at position 43

Any ideas?

What mongo version are you using? Trying to test this locally.

I am using next version: 4.2.8

Ok, you can use an expresion like this {{JSON.stringify({ "active": true, "createdAt": { $gte : "2021-01-12T20:15:31Z"} })}}

Thanks @RicardoE105.

I have checked and query is returning no data.
image

If I remove “createdAt” section, data is returned correctly.

Perhaps cuz there is not results that are active:true after that date?

Hi Ricardo,

There are results after date.
I will do some more tests.

Thank you!

1 Like

Hi Miquel, am I having the same problem as you? I’ve searched a lot on the internet but I haven’t found a solution. Did you manage to get around this problem? If yes, could you help me?

Thank you very much in advance.

Welcome to the community @Davi_Gomes

Not completely sure, but that is likely caused by the date not being a date object. If I remember correctly you have to send a Date object instance (see example below). If that is the case there is currently no way to parse the date from the Query box.

db.mycollection.find({
    "active": true,
    "createdAt" : {"$gte": new Date("2021-01-12T00:00.00Z")}
})

Thank you very much @RicardoE105, I am happy to be a part.

In fact your query is correct, but in the Query box when I instantiate the Date object, the query doesn’t work. I tried using JSON.stringify but it doesn’t work either because the new Date is processed and turns into a string.
For now I had to convert the field to timestamp and make the query comparing integers. But I would like to use it as a date, I have other situations where I won’t be able to get away from it.

Thank you in advance for the return.

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.

When Will this be supported?

In the MongoDB documentation there is support for Extended JSON - Node driver should support this already. And so, a Date could be represented by:

{"$date":"2019-08-11T17:54:14.692Z"}
1 Like

Hello,

We too are facing this issues, with no apaprent solutoin, thoug it is quite a bug limiation unfortuntely ! Has anyone got a workaround / solution since ? :slight_smile:

Hello all,

I am also facing the similar issue to filter data on the basis of date. Is there any update or work around this bug?

The query I am using is -

{"reviewDate":{"$lte": new Date({{new Date().toISOString()}})}}

I usually post-filter results by date to bypass this limitation.

But if you have lots of documents, it will affect n8n performance.

You can use find with $expr to parse date parameters with $dateFromString and query documents

Example query date between 2021-11-11 - 2021-12-12

{
    "$expr":{
        "$and":[
        	{"$lt":["$createdAt",{ "$dateFromString": { "dateString": "2021-12-12T12:12:12.123Z"}}]},
            {"$gt":["$createdAt",{ "$dateFromString": { "dateString": "2021-11-11T12:12:12.123Z"}}]},
        ]
    }
}

another example with using the output of node Date & Time as a parameter. do not forget to add quotes around the expresssion

{
  "$expr": {
    "$and": [
      {
        "$gt": [
          "$createdAt",
          {
            "$dateFromString": {
              "dateString": "{{$node["Date & Time"].json["lastDay"]}}"
            }
          }
        ]
      }
    ]
  }
}
2 Likes

Hey @ozanerturk, thanks so much for this workaround! I have been using the Function node for such queries until now, but your approach is much more elegant.

Note for myself: Although the field is named “createdAt”, in the query it has to be “$createdAt”. (Took one hour to figure this out) :worried:

Hey, I am also facing this problem, which is quite frustrating given how basic this requirement is. We are looking to query a date based on ISODate mongo type, which is not supported in the n8n query UI. I also tried the approaches above without luck. Lastly, I tried doing an $aggregate function which didn’t work either. My very last attempt was to hardcode the date and I see “no output returned”. This is my query:
{“createdAt” : {“$gt”: “2022-04-11T17:54:14.692Z”}}
I ran it on mongo cli and got the intended result