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
randomness