How to Query MongoDB on a Date Column in Talend Big Data?

Anonymous — Sat, 16 Nov 2024 11:28:38 GMT

Hi,
Lets say I insert a date into MongoDB using tMongoDBConnection-->tFixedFlowInput-->tMongoDBOutput, using TalendDate.parseDate("yyyy-MM-dd","2014-01-01").
I can go into the Mongo shell, and query the collection:

I can see that the record has been inserted using ISODate.
In the shell I would query it like this ( {dt:ISODate("2014-01-01T08:00:00Z")} ) :

However, if I write the identical query in tMongoDBInput:

I receive the following error:
Exception in component tMongoDBInput_1
com.mongodb.util.JSONParseException:
{'dt':ISODate('2014-01-01T08:00:00Z')}
      ^
    at com.mongodb.util.JSONParser.parse(JSON.java:198)
    at com.mongodb.util.JSONParser.parseObject(JSON.java:231)
    at com.mongodb.util.JSONParser.parse(JSON.java:195)
    at com.mongodb.util.JSONParser.parse(JSON.java:145)
    at com.mongodb.util.JSON.parse(JSON.java:81)
    at com.mongodb.util.JSON.parse(JSON.java:66)
    at primehome.testmongodate_0_1.TestMongoDate.tMongoDBInput_1Process(TestMongoDate.java:605)
    at primehome.testmongodate_0_1.TestMongoDate.tMongoDBConnection_1Process(TestMongoDate.java:371)
    at primehome.testmongodate_0_1.TestMongoDate.runJobInTOS(TestMongoDate.java:989)
    at primehome.testmongodate_0_1.TestMongoDate.main(TestMongoDate.java:854)
This question has been asked on the Talend Forum https://community.talend.com/t5/Design-and-Development/tMongoDBInput-query-for-date-gte/td-p/85560 but I was unable to get the answer, which said to query using the "$date" operation, to work.
In the Mongo Shell, I cannot use the query {'dt': {'$date':'2014-01-01T08:00:00Z'}} without the following error:

"Can't canonicalize query: BadValue unknown operator: $date".
In the mongoshell, If I do a query like {dt: {$lte : {$date : '2020-01-01T00:00:00Z'}}} I receive no error, but I also receive no document:

Is there a way to query MongoDB for dates in Talend?
Regards,
Matthew

Re: How to Query MongoDB on a Date Column in Talend Big Data?

Anonymous — Wed, 08 Oct 2014 07:16:45 GMT

Perhaps the MongoDB driver works a bit different as the shell. Why do you quote the attribute name? Actually this is not necessary and I guess this is the problem.

Re: How to Query MongoDB on a Date Column in Talend Big Data?

krengan21 — Wed, 16 Nov 2016 05:08:22 GMT

How to get the previous date data from mongodb using talend i am also getting the same error in tmongodbinput component

Re: How to Query MongoDB on a Date Column in Talend Big Data?

Anonymous — Wed, 16 Nov 2016 07:43:02 GMT

You have to set the attribute names and string values in double quotes like this:

"{\"id\":\"my_value\"}"

topic Re: How to Query MongoDB on a Date Column in Talend Big Data? in Talend Studio

How to Query MongoDB on a Date Column in Talend Big Data?

Re: How to Query MongoDB on a Date Column in Talend Big Data?

Re: How to Query MongoDB on a Date Column in Talend Big Data?

Re: How to Query MongoDB on a Date Column in Talend Big Data?