tMongoInput Query Parse Error

Highlighted
Six Stars

tMongoInput Query Parse Error

i am trying to few records from mongodb collection using below mongo query which is running fine in mongo console or studio3t.

 

"{
begin_date : {
$gte: new Date(new Date().setDate(new Date().getDate()-30))
}
}"

 

But it giving the error as 

Exception in component tMongoDBInput_3 (All_Tables)
org.bson.json.JsonParseException: JSON reader expected an integer or a string but found 'new'.
	at org.bson.json.JsonReader.visitDateTimeConstructor(JsonReader.java:923)
	at org.bson.json.JsonReader.visitNew(JsonReader.java:554)

 

Any help.


Accepted Solutions
Nine Stars

Re: tMongoInput Query Parse Error


it's not returning any rows.

[statistics] connecting to socket on port 3702
[statistics] connected
[statistics] disconnected

Do you actually have any data where the begin_date is greater than or equal to today?  Try changing gte to lte to see if data is returned to confirm that my suggested syntax works.

 

You can use the TalendDate.addDate function to change the date.  For example, the code below decrements the day by one, e.g. yesterday:

"{
begin_date:
{
$gte:{$date:'"+TalendDate.formatDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",TalendDate.addDate(new java.util.Date(),-1,"dd"))+"' }
}
}"

There is documentation on all the TalendDate functions here:

https://help.talend.com/reader/TakcAFoOnWMPdNo8XXlPpg/mOrhvhXRdRX1oRzaNEkNvw

Although the documentation for the addDate function does not seem correct.  It actually takes a Date as an argument but the documentation shows that it takes a date as a string and a format.  If you use my addDate example above it will work.

--
Please give Kudos and mark topics as solved where appropriate.

All Replies
Nine Stars

Re: tMongoInput Query Parse Error

The are a lot of existing threads on this subject,  did you see this one:

 

https://community.talend.com/t5/Design-and-Development/Selecting-data-from-MongoDB/m-p/61270/highlig...

 

The author in the comment says this syntax worked, for example, to get all records where lastModifiedDate is gte now:

"{lastModifiedDate:
{$gte:{$date:'"+TalendDate.formatDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",new java.util.Date())+"'
}}}"

 

 

 

--
Please give Kudos and mark topics as solved where appropriate.
Six Stars

Re: tMongoInput Query Parse Error

Thanks for the reply @nfz11 

 

I did tried what you given.

 

it's not returning any rows.

[statistics] connecting to socket on port 3702
[statistics] connected
[statistics] disconnected
"{
begin_date:
{
$gte:{$date:'"+TalendDate.formatDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",new java.util.Date())+"' }
}
}"

And how do i can get previous day records from the same.

 

 

Nine Stars

Re: tMongoInput Query Parse Error


it's not returning any rows.

[statistics] connecting to socket on port 3702
[statistics] connected
[statistics] disconnected

Do you actually have any data where the begin_date is greater than or equal to today?  Try changing gte to lte to see if data is returned to confirm that my suggested syntax works.

 

You can use the TalendDate.addDate function to change the date.  For example, the code below decrements the day by one, e.g. yesterday:

"{
begin_date:
{
$gte:{$date:'"+TalendDate.formatDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",TalendDate.addDate(new java.util.Date(),-1,"dd"))+"' }
}
}"

There is documentation on all the TalendDate functions here:

https://help.talend.com/reader/TakcAFoOnWMPdNo8XXlPpg/mOrhvhXRdRX1oRzaNEkNvw

Although the documentation for the addDate function does not seem correct.  It actually takes a Date as an argument but the documentation shows that it takes a date as a string and a format.  If you use my addDate example above it will work.

--
Please give Kudos and mark topics as solved where appropriate.

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

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog