Search code examples
mongodbpentahokettlepentaho-spoonpentaho-data-integration

Pentaho Data Integrator - MongoDB Input - JSONParseException

I'm trying to use the MongoDB input step in Pentaho 9.4.

It's my first time working with MongoDb. I made a simple query, and ran it in RobotMongo.

db.history.aggregate( [ { $match: {_insertedDate: {$gte:ISODate("2023-07-26T00:00:00Z")}} }, { $group:{_id:{originalSummary:"$originalSummary"}} } ] )

Returns values normally. Result

When taking this same query, and puting it to the PDI, I can't run it. What am I doing wrong?

Error:

2023/07/26 19:54:36 - MongoDB input.0 - ERROR (version 9.4.0.0-343, build 0.0 from 2022-11-08 07.50.27 by buildguy) : org.pentaho.di.core.exception.KettleException: 
2023/07/26 19:54:36 - MongoDB input.0 - com.mongodb.util.JSONParseException: 
{ $match: {_insertedDate: {$gte: ISODate("2023-07-26T00:00:00Z")}} }
                                ^
2023/07/26 19:54:36 - MongoDB input.0 - 
{ $match: {_insertedDate: {$gte: ISODate("2023-07-26T00:00:00Z")}} }
                                ^
2023/07/26 19:54:36 - MongoDB input.0 - 
2023/07/26 19:54:36 - MongoDB input.0 -     at org.pentaho.di.trans.steps.mongodbinput.MongoDbInput.processRow(MongoDbInput.java:155)
2023/07/26 19:54:36 - MongoDB input.0 -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2023/07/26 19:54:36 - MongoDB input.0 -     at java.lang.Thread.run(Thread.java:745)
2023/07/26 19:54:36 - MongoDB input.0 - Caused by: com.mongodb.util.JSONParseException: 
{ $match: {_insertedDate: {$gte:ISODate("2023-07-26T00:00:00Z")}} }
                                ^
2023/07/26 19:54:36 - MongoDB input.0 -     at com.mongodb.util.JSONParser.parse(JSON.java:232)

Pentaho: Configuration

Solution

  • According to official MongoDB doc,

    mongosh wraps objects of Date type with the ISODate helper; however, the objects remain of type Date.

    My interpretation is that ISODate may not be available elsewhere. It may be sensible to use MongoDB extended JSON format for your case.