Pentaho MongoDB Report query date ranges

Question

I'm trying to generate a report using Pentaho Report Designer MongoDB native connector but I can't get it to work with date ranges.

My collection has a column named "timestamp", I tried to put the following in the query tab:

{"timestamp" : {$gte :ISODate("2013-12-04T15:10:00.000Z")}}
{"timestamp" : {$gte : new Date("2013-12-04T15:10:00.000Z")}}

while these queries both work using a .find(query) on the mongo shell, in the Pentaho Report Designer I get no rows and no error message.

Simpler queries like:

{"objType" : "Image"}

works fine in PRD.

EDIT:

I managed to get it work thanks to suggestions from:

http://wiki.pentaho.com/display/EAI/MongoDB+Input

So the answer is to use something like:

{ "$query" : { "timestamp" : { $gte : { $date : "2013-12-04T15:10:00Z" }, $lte : { $date : "2013-12-04T15:12:00Z" } } } }

Still I can't get it to work using Date parameters: If I put:

{ "$query" : { "timestamp" : { $gte : { $date : "${StartDate}" }, $lte : { $date : "${EndDate}" } } } }

In the query tab and I choose "StartDate" and "EndDate" to be of "Date" type it won't work. If I set the type of the paramters to "String" and set their value in ISO formats, it works correctly.

Is there a way to use the Date type for the parameters directly?

I'm trying to guess what's happening bc I don't know the connector but I understand mongo very well. ISODate is a native Mongo type, which means any query from any driver must convert dates in the specific language to an ISODate. In this case seems your conversion is not working very well. In Python it's easy to send a datetime value, also in NodeJS is quite straightforward. If you send a date you must provide a real date in your language unless there's a drive I don't know that converts strings to ISODate's — Maximiliano Rios
Pentaho Report Designer is Java based and uses the Mongo Java driver. — sandro.impastato
I'm reading the documentation and I find something weird. There's not anything like "$query" in Mongo so there's a transformation in there that could mess up the datetime. Have you tried separately "gte" in one query and "lte" in a second query to see if this works? — Maximiliano Rios
Yes I tried that, I think there's some error with how Pentaho Report Designer passes the Date object to the underlying Java driver when it performs the parameter substitution. Theoretically Pentaho should perform the correct parameter substitution according to the parameter type (for example in SQL data sources this works fine). — sandro.impastato

Eduardo Cuomo Eduardo Cuomo · Accepted Answer · 2019-12-03T13:39:06

From https://help.pentaho.com/Documentation/8.2/Products/Data_Integration/Transformation_Step_Reference/MongoDB_Input

You can use something like next:

{ created_at : { $gte : { $date : "2014-12-31T00:00:00.000Z" } } }

With variables:

# my_date = '2014-12-31'
{ created_at : { $gte : { $date : "${my_date}T00:00:00.000Z" } } }

Or:

# my_date_time = '2014-12-31T00:00:00.000Z'
{ created_at : { $gte : { $date : "${my_date_time}" } } }

Pentaho MongoDB Report query date ranges

1 Answers