One Star

Selecting data from MongoDB

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.
10 REPLIES
Seventeen Stars

Re: Selecting data from MongoDB

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 ...."
One Star

Re: Selecting data from MongoDB

Thank you it worked like a charm.

Re: Selecting data from MongoDB

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.
One Star

Re: Selecting data from MongoDB

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\"}} }"

One Star

Re: Selecting data from MongoDB

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\"}} }"
One Star

Re: Selecting data from MongoDB

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
    }
});"
One Star

Re: Selecting data from MongoDB

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)+"' }}}"
One Star

Re: Selecting data from MongoDB

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.....
One Star

Re: Selecting data from MongoDB

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 Smiley Sad
Six Stars

Re: Selecting data from MongoDB

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.