Writing MongoDB queries in BIRT

Hi ,
 
I am new to BIRT so pardon my lack of knowledge . I just cannot figure out how to write MongoDB queries in BIRT . I connected to a MongoDB data source and thats where the problem begins ! 
 
I have a collection with certain data . Using mapReduce i can get the required fields that i need to use in building my chart.But i do not know how to build the mapReduce query in birt.
 
Should i select my collection form the drop down ? Or do i need to select run database commands ? Where do i write my mongodb queries ? Do i write them as it is or would I need modifications ? 
 
Wherever i write my queries in BIRT I get an error saying : error in parsing JSON expression.
 
Please guide ! Waiting for your reply !

Find more posts tagged with

Comments

pricher

Hi,
 
After selecting your collection from the drop down, select MapReduce in the Command Type drop down. Then click on Expression and write your Map Reduce command in the editor. You can also import existing MapReduce command directly in the editor.

navdeeppathania

Thanks so much Pricher for your prompt reply.. I guess that will do. I'll get back if i have more questions . Thanks !

navdeeppathania

Hi i face another error !
 
This is my aggregate expression :
<pre class="_prettyXprint _lang-">
{
'$match':{
'$and':[
{'user_id':1},
{
'dateRange':{
'$gt':{$date :'2015-04-04T00:00:00.000Z'},
'$lt':{$date:'2015-05-05T23:49:59.999Z'}
}
}

]
}
},
{
'$group' :
{
'_id' : "$name",
'count' : {$sum : 1}
}
}
</pre>
 Now i need to replace the dateRange by my report parameters . I created to 2 report parameters as shown below with the same exact dates just to check if my report works. It works without the report parameters . The data i have does lie between the range.
 
<img src="http://www.kriblog.com/user/deepnav/birt-from-1432887248272.png" alt="birt-from-1432887248272.png">
 
 
<img src="http://www.kriblog.com/user/deepnav/birt-to-1432887257950.png" alt="birt-to-1432887257950.png">
 
 
This is my beforeOpen script for my dataset :
<pre class="_prettyXprint _lang-">
startDay = params["from"].value.getDate();
startMonth = params["from"].value.getMonth() + 1;
startYear = BirtDateTime.year(params["from"]);

replaceQueryStartDate = "" + startYear + "-" + startMonth + "-" + startDay;
this.queryText = this.queryText.replace("2015-04-04T00:00:00.000Z", replaceQueryStartDate);

endDay = params["to"].value.getDate();
endMonth = params["to"].value.getMonth() + 1;
endYear = BirtDateTime.year(params["to"]);

replaceQueryEndDate = "" + endYear + "-" + endMonth + "-" + endDay;
this.queryText = this.queryText.replace("2015-05-05T23:49:59.999Z", replaceQueryEndDate);
</pre>
But my report is just blank . No chart is shown . There are no errors shown either . 
 
I am unable to find the error kindly provide inputs.
 
Also i wanted to know after i create my expressions in the query tab , of what use is the query text provided in the property binding tab ?
 
Any help would be really appreciated.

Clement Wong

In the expression builder, the ODA uses a JSON parser in a strict mode. To use dates in a strict mode (<a data-ipb='nomediaparse' href='http://docs.mongodb.org/manual/reference/mongodb-extended-json'>http://docs.mongodb.org/manual/reference/mongodb-extended-json</a>), we need to use the format { "$date": <date> } where <date> is the JSON representation of a 64-bit signed integer for milliseconds since epoch UTC (unsigned before version 1.9.1). 

Example:
<pre class="_prettyXprint">
{"orderDate":{"$gt": { "$date": 1369266840962 }}}
</pre>
To parameterize this, you can add a Report Parameter like you did as a DateTime type. Then, update the expression to hold a substring that will be replaced such as â€œ9999â€. Example: {"orderDate":{"$gt": { "$date": 9999 }}} 
In the beforeOpen event of the Data Set, add code that will read the Report Parameter value, convert to milliseconds since epoch UTC, and replace the query expression with the user requested value.
<pre class="_prettyXprint">
this.queryText = this.queryText.replace("9999", params["pDate"].value.getTime().toString());</pre>

navdeeppathania

Thank you Clement , this was extremely helpful . I could not have imagined a better community support .
 
Cheers !!
 
PS : I will be coming back

navdeeppathania

Hi all , 
 
I am facing a situation where I perform calculations using javascript and use the result to perform aggregation. Below is the code :
<pre class="_prettyXprint _lang-">
var ranges = [ 15,20,25,30,35,40];

var rangeProj = {
"$concat": []
};

for (i = 1; i < ranges.length; i++) {
rangeProj.$concat.push({
$cond: {
if: {
$and: [{
$gte: [ "$age", ranges[i-1] ]
}, {
$lt: [ "$age", ranges[i] ]
}]
},
then: ranges[i-1] + "-" + ranges[i],
else: ""
}
})
}

db.myCollection.aggregate([{
$project: { "_id": 0, "range": rangeProj }
}, {
$group: { _id: "$range", count: { $sum: 1 } }
}, {
$sort: { _id : 1 }
}]);
</pre>
Is it possible to do the above in BIRT ? 
 
Kindly provide inputs.

Clement Wong

Unfortunately, that JavaScript syntax is not supported at this time. However, we can execute the code within the aggregate command if it were expanded out. Choose Command Operation > Command type: Aggregate and then update the Expression...
 
In your case, it would something like this:
<pre class="_prettyXprint">
{ $project: {
"_id": 0,
"range": {
$concat: [{
 $cond: [ { $and: [
 { $gte: ["$age", 15] },
 { $lt: ["$age", 20] }
 ] }, "range 15-20", "" ]
}, {
 $cond: [ { $and: [
 { $gte: ["$age", 20] },
 { $lt: ["$age", 25] }
 ] }, "range 20-25", "" ]
},
...
{
 $cond: [ { $and: [
 { $gte: ["$age", 35] },
 { $lt: ["$age", 40] }
 ] }, "range 35-40", "" ]
}
]
}
}},
{ $group: { _id: "$range", count: { $sum: 1 } } },
{ $sort: { "_id": 1 } }</pre>

navdeeppathania

Thank you so much Clement for taking the time to hard code this . I reallly appreciate it .
 
I have a java middleware so i achieved the above by storing the whole of my code in a function on the server and then using :
<pre class="_prettyXprint _lang-">
db.eval()
</pre>
in my java .
 
That of course would consume time and it involves some work on scripting the dataset too . The solution you provided will help me a great deal in my further development tasks. 
 
Thanks so much again !

richard.bibb

<pre class="_prettyXprint _lang-">
Removed...</pre>

richard.bibb

I'm struggling with getting parameters through to my MongoDB query. I'm using an Aggregate Expression which contains the following
 
<div> $match: {</div>
<div> "strategyName": "us alpha",</div>
<div> "basket.assetId":AssetIdParam</div>
<div> }</div>
<div> </div>
<div>I have a parameter defined called pAssetId</div>
<div> </div>
<div>and I have set the "Before Open" property on my Data Set to </div>
<div> </div>
<div>this.queryText = this.queryText.replace("AssetIdParam", params["pAssetId"].value)</div>
<div> </div>
<div>I edit the dataset to insert the string "AssetIdParam" as shown above (DataSet->Edit->Expression) and then close the editor. At that point I'm asked if I want to refresh the available fields, which I do. This produces an error saying "Unable to find available fields" and I'm shown the query. This query does not have the variable AssetIdParam replaced so I'm guessing that the replace hasn't worked.</div>
<div> </div>
<div>Does anyone have some insight as to what I'm doing wrong?</div>
<div> </div>
<div> </div>
<div>I also have another question where I'm not at all sure what to do. The AssetId above is the numeric primary key on the collection. This collection does not have a field containing the Asset Name (i.e. the human readable information). I have populated the combo box of my parameter with the Asset Names obtained from another collection, this contains the mapping between AssetId and AssetName. What I would like to do is to display a list of Asset Names to the user and, once one is selected, insert the corresponding Asset Id in the query above. Is this possible?</div>

Clement Wong

What data type is AssetIdParam? What are possible values?
 
If it's an integer, for the time being, can you try the following for your expression:
<pre class="_prettyXprint">
$match: {
 "strategyName": "us alpha",
 "basket.assetId": 888
 }</pre>
And in your beforeOpen event, use:
<pre class="_prettyXprint">
this.queryText = this.queryText.replace("888", params["pAssetId"].value);</pre>
Or if it's a string, can you try the following for your expression:
<pre class="_prettyXprint">
$match: {
 "strategyName": "us alpha",
 "basket.assetId": "888"
 }</pre>
And in your beforeOpen event, use:
<pre class="_prettyXprint">
this.queryText = this.queryText.replace("888", params["pAssetId"].value);</pre>
As for your other question, you can define your Asset Name parameter with a display value, and an actual value, which can be different from the display value. For example, display "New York", but value is 1 or display "San Francisco" but value is 2.
 
That parameter can then be referenced by it's display value, and value:
<pre class="_prettyXprint">
params["pAssetId"].displayText;
params["pAssetId"].value;</pre>

richard.bibb

Thanks for your response Clement. It was extremely helpful.
 
I realise now the subtlety of what I was doing wrong. Not only does the replacement value have to have the correct type it must also be valid for the query being executed. If it isn't the Data Set editor will not be able to display the available fields correctly.

Guido

Hi, i have a question: 
 
I need replace boolean type on beforeOpen, but i can't. 
 
I try something like this:
<pre class="_prettyXprint">
this.queryText = this.queryText.replace("999", params["myParam"].value)
</pre>
and
<pre class="_prettyXprint">
this.queryText = this.queryText.replace(true, params["myParam"].value)
</pre>
myParam.value is true/false.
 
If is not possible, what i can do?
 
Thanks for help me!!!
 
PD: sorry for my basic English ^_^

Clement Wong

You are replacing text, so you'll want to try:
<pre class="_prettyXprint">
this.queryText = this.queryText.replace("true", params["myParam"].value);</pre>
Or:
<pre class="_prettyXprint _lang-">
this.queryText = this.queryText.replace("true", params["myParam"].value.toString());
</pre>

Guido

Thank you very much Clement !!

Works fine now. Thanks again.