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

One Star

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

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 here 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
Seventeen Stars

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

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.
Seven Stars

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

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

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

You have to set the attribute names and string values in double quotes like this:
"{\"id\":\"my_value\"}"

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads