How to query mongo ISODate data with a parameter

Table of Contents 

Issue Description:

There are multiple ways to use a parameter in your query when using mongo ISODate data formatted like:

"timestamp" : ISODate("2009-01-14T00:26:04Z")

Resolution:

  • Use the jrxml below to render a query like the following in mongo:

    query test.logs query: {
    cs-bytes: 420, timestamp: {
        $gte: new Date(1006957607188)
    }
}
    <parameter name="startdate" class="java.util.Date">
<defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>
<queryString language="MongoDbQuery">
<![CDATA[{
collectionName : 'logs',
limit : 30,
 
findQuery : {
'timestamp' : {'$gte':$P{startdate}}
}
}]]>

  • If it is necessary that you pass a function, like the mongo $date function, through the query executor to your database, you can create a java.lang.String parameter and use concatenation to build the single string containing both the $date as well as your date-string:

    <defaultValueExpression>
    <![CDATA["$date: \"" + $P{stringDate} + "\""]]>
</defaultValueExpression>

    In the above case the $P{stringDate} needs to be a java.lang.String representation of your date in the ISODate format, you can use java api methods to get it formatted correctly. Something like the following might work with a java.util.Date, userdate, depending on your format:

    <parameter name="stringDate" class="java.lang.String" isForPrompting="false" />
<defaultValueExpression>
    <![CDATA[new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'").format($P{userdate})]]>
</defaultValueExpression>

Ref. Case #00041256

Feedback