topic Selecting data from MongoDB in Talend Studio

Selecting data from MongoDB

Anonymous — Thu, 12 Sep 2013 22:46:03 GMT

All,
I'm creating a job which will be extracting data from mongodb daily, mapping json doc to flat table and inserting that into mysql.
Problem: I have to pass start and end date ( taken from globalMap.get("start") and globalMap.get("end") ) dates to mongodb query. What component can I use to do that ? The problem with tmongodbRow component is it allows parameters , and let say i can create a javascript function with find query in it , but output document has to be mapped to a flat resultset. Unfortunately only tmongodbinput has this mapping ability. i can take fields from sub document and map them to a flat structure. BUT tmongodbinput does not allow parameters to be passed to a query.
I'm sure someone has faced this issue, i would really appreciate your response.
The solution ( sub optimal ) I've came up with is first use tmongodbrow and execute javascript function which extracts data and inserts it into a temporary mongodb collection, then next component tmongodbinput extracts all data and maps it into flat table. The problem with this approach is that db.insert(... ) in mongodb locks the database..... I would like to just run select from mongodb with "where" and avoid inserts.

Re: Selecting data from MongoDB

Anonymous — Thu, 12 Sep 2013 23:01:10 GMT

You can pass parameters to the query. Put your values from the globalMap as strings to your query:
"..... your query json expression...." + TalendDate.formatDate("yyyy-MM-dd", ((String) globalMap.get("start"))) + " .... continue with your json query ...."

Re: Selecting data from MongoDB

Anonymous — Thu, 12 Sep 2013 23:57:37 GMT

Thank you it worked like a charm.

Re: Selecting data from MongoDB

Anonymous — Fri, 11 Apr 2014 18:50:52 GMT

How did you manage to do it?
Right now I´m trying to do this query: "db.user_ban_entities.find({end_date: {$gte:ISODate()}})" that works perfectly in mongodb shell.
However including it in Talend Mongo Input results in mongodb.util.JSONParseException.

Re: Selecting data from MongoDB

Anonymous — Wed, 07 Jan 2015 13:02:09 GMT

Can someone please help me on passing date parameters to MongoDB through tMongoDBInput Component to retrieve values for a date range.
While passing the hardcoded value, its returning results. But while passing the same dates through context variable its not working.
with context variable:
"{EchoTimeStamp : { $gte :{\"$date\":\"+context.StartDate+\"}, $lte :{\"$date\":\"+context.EndDate+\"}} }"
with hardcoded value:
"{EchoTimeStamp : { $gte :{\"$date\":\"2014-12-08T00:00:00Z\"}, $lte :{\"$date\":\"2014-12-12T00:00:00Z\"}} }"

Re: Selecting data from MongoDB

Anonymous — Mon, 09 Mar 2015 22:30:48 GMT

I have same question, can someone please help.
Can someone please help me on passing date parameters to MongoDB through tMongoDBInput Component to retrieve values for a date range.
While passing the hardcoded value, its returning results. But while passing the same dates through context variable its not working.
with context variable:
"{EchoTimeStamp : { $gte :{\"$date\":\"+context.StartDate+\"}, $lte :{\"$date\":\"+context.EndDate+\"}} }"
with hardcoded value:
"{EchoTimeStamp : { $gte :{\"$date\":\"2014-12-08T00:00:00Z\"}, $lte :{\"$date\":\"2014-12-12T00:00:00Z\"}} }"

Re: Selecting data from MongoDB

_AnonymousUser — Wed, 06 May 2015 11:43:30 GMT

I have same question, can someone please help.
Can someone please help me on passing date parameters to MongoDB through tMongoDBInput Component to retrieve values for a date range.
While passing the hardcoded value, its returning results. But while passing the same dates through context variable its not working.
with context variable:
"{EchoTimeStamp : { $gte :{\"$date\":\"+context.StartDate+\"}, $lte :{\"$date\":\"+context.EndDate+\"}} }"
with hardcoded value:
"{EchoTimeStamp : { $gte :{\"$date\":\"2014-12-08T00:00:00Z\"}, $lte :{\"$date\":\"2014-12-12T00:00:00Z\"}} }"

Please use the below command in the query window to get it done.
"{EchoTimeStamp : { $gte :{$date:'"+context.StartDate+"'}, $lte :{$date:'"+context.EndDate+"'}} }"

Just you need to remove the escape characters and need to give a single quote on both sides of context variable.

If you need a work around for this also.You can use tmongoDBRow component and use the function window to write mongodb compatible commands to get it executed and furnish the resulset for you if Query is not working for you.
like
"var today = new Date();
var yesterday = new Date();
yesterday.setDate(today.getDate() - 1);
db.sessionLogs.find({
created: {
$gte: today,
$lt: yesterday
}
});"

Re: Selecting data from MongoDB

Anonymous — Tue, 16 Jun 2015 21:41:27 GMT

Actually Mongo has ISO date format and if you pass string format of date to $date function then it should work. Only glitch is though $date function returns NULL if you dont pass date in proper string format. Sample query which I use is as below. This works perfectly fine.
"{lastModifiedDate:{$gte:{$date:'"+TalendDate.formatDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",(Date)context.lastRuntime)+"' }}}"

Re: Selecting data from MongoDB

_AnonymousUser — Tue, 19 Jan 2016 11:16:57 GMT

Hi,
Im trying to use context variable in mongo query using mongodb input component
Eg: "{'_createdAt': { $gte :{$date:'"+context.incrementaldate1+"'}}}"
but im getting error like Type mismatch : cannot convert from string to boolean
In the above query im just trying to extract data above a particular date
Pls guide me.....

Re: Selecting data from MongoDB

Anonymous — Tue, 26 Apr 2016 22:40:29 GMT

Hello,
I am getting a same error as Chakravarthy. my query is
"{'data.type': 'provider', '_id': { $gt: { '$oid': '" + Integer.toHexString(((int)Math.floor(new Date().getTime()/1000)) - 60*60) + "0000000000000000'}}}"
I get the cannot convert from string to boolean error. I followed it to the Java code here is the problem:
if ("{'data.type': 'provider', '_id': { $gt: { '$oid': '"+ Integer.toHexString(((int) Math.floor(new Date()
.getTime() / 1000))- (60 * 60))+ "0000000000000000"+ "'}}}".matches(".*" + key + "*:.*"))
So basically the java code gets generated such that there is a string in an if condition which will as predicted throw an error.
Can someone help out with this? I haven't yet found a resource explaining this

Re: Selecting data from MongoDB

krengan21 — Wed, 16 Nov 2016 05:27:23 GMT

hi,
I am trying to get the data of the previous date but i am getting the error in tmongodbinput component.What needs to be done to load only the previous day date using talend.

Re: Selecting data from MongoDB

raowaqasakram — Tue, 16 Aug 2022 13:19:52 GMT

I have created a context variable .

Data type is String

String last_sync_timestamp= "2022-08-16T00:00:00.533Z" ;

Query :

{'timestamp': {$gt: {$date: '"+context.last_sync_timestamp+"'}

This worked absolutely fine for me in TOS 8.0.