AnsweredAssumed Answered

Using a MongoDB Input Step Aggregation Pipeline

Question asked by Roguen Keller Employee on May 17, 2018
Latest reply on May 18, 2018 by Roguen Keller


I am currently using a MongoDB Input step in my transformation.

My query is fairly simple.  It looks like this:

{

      activity-activityTime-timestamp:{

           "$gt" : ${ptr_timerange},

           "$lte" : ${ptr_timestamp}

          }

}

With two variables for my timestamp range.

 

But I would like to do two things with my results:

  1. Sort them by the timestamp descending
  2. Take only the first result

My searching indicates that I should be checking the box for an aggregation pipeline in this step, but I cannot seem to get the syntax correct. 

I've attempted something like this unsuccessfully (with obvious hard coded values to stand in for the timestamp variables):

{

  $match: {

    activity-activityTime-timestamp:{

      "$gt" : 1520055629221,

      "$lte" : 1526062848202

    }

  }

},

{

  $orderby: { activity-activityTime-timestamp : -1 }

},

{

  $limit: 1

}

Even if I trim this down to the match portion of the JSON I error out. 

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - Configuring connection with read preference: primary

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - No read preference tag sets defined

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - Configuring connection with write concern - w = 1, wTimeout = 0, journaled = false

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : Unexpected error

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : org.pentaho.di.core.exception.KettleException:

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - com.mongodb.MongoCommandException: Command failed with error 9: 'The 'cursor' option is required, except for aggregate with the explain argument' on server localhost:27017. The full response is { "ok" : 0.0, "errmsg" : "The 'cursor' option is required, except for aggregate with the explain argument", "code" : 9, "codeName" : "FailedToParse" }

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - Command failed with error 9: 'The 'cursor' option is required, except for aggregate with the explain argument' on server localhost:27017. The full response is { "ok" : 0.0, "errmsg" : "The 'cursor' option is required, except for aggregate with the explain argument", "code" : 9, "codeName" : "FailedToParse" }

2018/05/17 14:58:25 - Use TS to Query View Activities.0 -

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at org.pentaho.di.trans.steps.mongodbinput.MongoDbInput.processRow(MongoDbInput.java:137)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at java.lang.Thread.run(Unknown Source)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - Caused by: com.mongodb.MongoCommandException: Command failed with error 9: 'The 'cursor' option is required, except for aggregate with the explain argument' on server localhost:27017. The full response is { "ok" : 0.0, "errmsg" : "The 'cursor' option is required, except for aggregate with the explain argument", "code" : 9, "codeName" : "FailedToParse" }

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.connection.ProtocolHelper.getCommandFailureException(ProtocolHelper.java:115)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.connection.CommandProtocol.execute(CommandProtocol.java:114)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.connection.DefaultServer$DefaultServerProtocolExecutor.execute(DefaultServer.java:159)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.connection.DefaultServerConnection.executeProtocol(DefaultServerConnection.java:286)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.connection.DefaultServerConnection.command(DefaultServerConnection.java:173)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.operation.CommandOperationHelper.executeWrappedCommandProtocol(CommandOperationHelper.java:215)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.operation.CommandOperationHelper.executeWrappedCommandProtocol(CommandOperationHelper.java:206)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.operation.CommandOperationHelper.executeWrappedCommandProtocol(CommandOperationHelper.java:112)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.operation.AggregateOperation$1.call(AggregateOperation.java:227)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.operation.AggregateOperation$1.call(AggregateOperation.java:223)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.operation.OperationHelper.withConnectionSource(OperationHelper.java:239)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.operation.OperationHelper.withConnection(OperationHelper.java:212)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.operation.AggregateOperation.execute(AggregateOperation.java:223)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.operation.AggregateOperation.execute(AggregateOperation.java:65)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.Mongo.execute(Mongo.java:772)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.Mongo$2.execute(Mongo.java:759)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.DBCollection.aggregate(DBCollection.java:1377)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.DBCollection.aggregate(DBCollection.java:1308)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.DBCollection.aggregate(DBCollection.java:1294)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at com.mongodb.DBCollection.aggregate(DBCollection.java:1282)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at org.pentaho.mongo.wrapper.collection.DefaultMongoCollectionWrapper.aggregate(DefaultMongoCollectionWrapper.java:47)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at org.pentaho.di.trans.steps.mongodbinput.MongoDbInput.initQuery(MongoDbInput.java:191)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - at org.pentaho.di.trans.steps.mongodbinput.MongoDbInput.processRow(MongoDbInput.java:79)

2018/05/17 14:58:25 - Use TS to Query View Activities.0 - ... 2 more

Is this just a syntax issue or something else?

 

This is Pentaho PDI 7.0 EE

Outcomes