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
Highlighted
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.
Eight 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\"}"

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog

Talend Integration with Databricks

Take a look at this video about Talend Integration with Databricks

Watch Now