Using a date parameter with mongodb aggregation

I'm trying to write a report that gets data from a mongodb using Birt 4.3.The documents which record logins look like this [font="'courier new', courier, monospace;"]{ 
"_id" : ObjectId("5208ff1c2d5df25629d115bf"), 
"status" : "A", 
"accountNo" : "UY675820415307047356",[/font][font="'courier new', courier, monospace;"] "location" : "UK" 
"timestamp" : ISODate("2013-08-12T15:28:28.388Z") 
}[/font] My issue is that I need to present the data in the following way:1) limit data by a time period defined by 2 report parameters startDate & endDate2) group the data by accountNo, counting the logins for each account and also the total logins So, I have tried to use the Command Expression of the query builder with the following expression: [font="'courier new', courier, monospace;"]{ $match: { timestamp: {$gte:'?',$lte:'?'} } }, 
{ $group : 
{ _id : 
{ accountNo : "$accountNo", 
location: "$location" 
}, 
totalSignonsPerAccount : { $sum : 1}, 
totalSignons : { $sum : "$totalSignonsPerAccount"} 
} 
}[/font] The $group part of the aggregation pipeline works fine, but I cannot figure out how to write the $match portion. If I change the $match to use the status property e.g. [font="'courier new', courier, monospace;"]{ $match: { status: 'A' } },[/font] it works fine, but I can't for the life of me figure out how to get it to work with date parameters against the timestamp property . I have the same date parameters in filters on other data sets that don't require any aggregation and they work without issue. Anyone got any clues? Cheers

Find more posts tagged with

Comments

kclark

In the output columns section of the data set UI what is the type set to for your date field?

malcatron

The timestamp field is not part of the resulting data set, so it doesn't appear in the output columns section. I am trying to get a subset of the collection based on a time period and then perform aggregation on that subset. i.e. how many times did each account login between startDate and endDate Using the mongodb shell I can run the query I need (endDate omitted for brevity):[color=#0000cd;][font="'courier new', courier, monospace;"]db.signonLog.aggregate({ $match: { timestamp: {$gte : new ISODate("2013-08-12T15:28:28.412Z") } } }, 
{ $group : 
{ _id : 
{ accountNo : "$accountNo", 
location: "$location" 
}, 
totalSignonsPerAccount : { $sum : 1}, 
totalSignons : { $sum : "$totalSignonsPerAccount"} 
} 
})[/color][/font] But in Birt I just cannot figure out how to do the date comparison. Basically, I think that firstly I need to understand how do you represent a date in Birt for Mongodb i.e.[color=#0000cd;][font="'courier new', courier, monospace;"]timestamp: {$gte : [/color][/font][color=#ff0000;][font="'courier new', courier, monospace;"]<what goes here?>[/color][/font][color=#0000cd;][font="'courier new', courier, monospace;"]) }[/color][/font] Thanks!

Oculis

Your query in Birt runs just as well using $date as follows:<pre class="_prettyXprint">{ $match: { timestamp: { $gte: { $date: "2013-08-12T15:28:28.412Z" } } } },{ $group : { _id : { accountNo : "$accountNo", location: "$location" }, totalSignonsPerAccount : { $sum : 1}, totalSignons : { $sum : "$totalSignonsPerAccount"} }})</pre>But the actual question (at least for me) is how to get variables working inside the query so I could something like [color=#0000ff;]{ $date: params["timestamp"].value }[/color] instead of [color=#0000ff;]{ $date: "2013-08-12T15:28:28.412Z" }[/color]. Unfortunately it does not work that way.

malcatron

Ok, it's a year later and I've had to revisit this issue for a similar report and have finally found a way around it! So to hopefully save someone else from tearing their hair out over this, here's how I did it...
 
For this report I needed to list the number of downloads and playbacks of each title between 2 dates supplied as parameters.
 
First create the parameters as type Date with a suitable format (e,g, dd-MM-yyyy)
 
Then create a MongoDb data set and set its command type to be aggregation.
 
Define its expression, using $date to create the dates (requires full ISODate i.e. 2014-05-01 will not work!)
<pre class="_prettyXprint _lang-js">
{ $match:
{
name: {$in:['download', 'playback'] },
tstamp: { $gte: { $date: "1970-01-01T00:00:00.000Z" }, $lte: { $date: "9999-01-01T23:49:59.999Z" }}
}
},
{ $group:
{
_id: { title:"$key", activity: "$name"},
count: { $sum: 1 }
}
}
</pre>
For the dates use extreme values as in the above example - this will ensure that Birt can get a good snapshot of your MongoDb docs to ascertain the fields available. Make a note of the exact dates used (e.g. 1970-01-01 & 9999-01-01) as we will now be using a script to do text replacement on the query expression (I know... :blink: ). Select the fields you want and finish creating the data set.
 
Select the data set in the outline pane then select the script tab of the main editor pane.
 
From the Script dropdown select beforeOpen and enter a script to replace the dates of the query expression with the report parameters (here: startDate and endDate) NB: Months start at 0 so need to add 1 to get correct value for text replace!!
<pre class="_prettyXprint _lang-js">
startDay = params["startDate"].value.getDate();
startMonth = params["startDate"].value.getMonth() + 1;
startYear = BirtDateTime.year(params["startDate"]);

replaceQueryStartDate = "" + startYear + "-" + startMonth + "-" + startDay;
this.queryText = this.queryText.replace("1970-01-01", replaceQueryStartDate);

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

replaceQueryEndDate = "" + endYear + "-" + endMonth + "-" + endDay;
this.queryText = this.queryText.replace("9999-01-01", replaceQueryEndDate);

vars["queryText"] = this.queryText;
</pre>
You may find it useful to use a variable to output the query text for debugging, above I have used a variable called "queryText" and then added it temporarily to my report for reference during development.
 
So, not particularly elegant but it works!