How to use date in PDI in MongoDB input step?

Question

I am using mongoDB input step in my transformation and the query i am using is not working. On mongoplayground and compass the query works fine, however the exact same query on kettle gives the following error

2020/11/26 11:41:10 - file:///home/stqa/Documents/PDI Transformations/Allticket.ktr : Allticket - Dispatching started for transformation [file:///home/stqa/Documents/PDI Transformations/Allticket.ktr : Allticket]
2020/11/26 11:41:10 - Get Data.0 - Normal authentication for user admin
2020/11/26 11:41:10 - Get Data.0 - Configuring connection with read preference: primary
2020/11/26 11:41:10 - Get Data.0 - No read preference tag sets defined
2020/11/26 11:41:10 - Get Data.0 - Configuring connection with write concern - w = 1, wTimeout = 0, journaled = false
2020/11/26 11:41:10 - Get Data.0 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : Unexpected error
2020/11/26 11:41:10 - Get Data.0 - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : org.pentaho.di.core.exception.KettleException: 
2020/11/26 11:41:10 - Get Data.0 - com.mongodb.util.JSONParseException: 
2020/11/26 11:41:10 - Get Data.0 - {$match: { ticket_date : { $gte : new Date("2020-11-26")}}}
2020/11/26 11:41:10 - Get Data.0 -                                    ^
2020/11/26 11:41:10 - Get Data.0 - 
2020/11/26 11:41:10 - Get Data.0 - {$match: { ticket_date : { $gte : new Date("2020-11-26")}}}
2020/11/26 11:41:10 - Get Data.0 -                                    ^
2020/11/26 11:41:10 - Get Data.0 - 
2020/11/26 11:41:10 - Get Data.0 -  at org.pentaho.di.trans.steps.mongodbinput.MongoDbInput.processRow(MongoDbInput.java:137)
2020/11/26 11:41:10 - Get Data.0 -  at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2020/11/26 11:41:10 - Get Data.0 -  at java.lang.Thread.run(Thread.java:748)
2020/11/26 11:41:10 - Get Data.0 - Caused by: com.mongodb.util.JSONParseException: 
2020/11/26 11:41:10 - Get Data.0 - {$match: { ticket_date : { $gte : new Date("2020-11-26")}}}
2020/11/26 11:41:10 - Get Data.0 -                                    ^

This error comes when i add the date match condition.I have tried new Date, ISOdate, encasing date in quotes, putting the match condition at different locations in the aggregate query etc. nothing is working

db.collection.aggregate([
  {
    $addFields: {
      content: {
        $cond: [
          {
            $eq: [
              {
                $type: "$content"
              },
              "array"
            ]
          },
          {
            $trim: {
              input: {
                $reduce: {
                  input: "$content",
                  initialValue: "",
                  in: {
                    $concat: [
                      "$$value",
                      " ",
                      "$$this"
                    ]
                  }
                }
              }
            }
          },
          "$content"
        ]
      }
    }
  },
  {
    $project: {
      heading: 1,
      content: 1,
      source: 1,
      ticket_date: 1,
      lang: 1
    }
  },
  {
    $match: {
      ticket_date: {
        $gte: new Date("2020-11-24")
      }
    }
  }
])

score 0 · Answer 1 · edited Nov 10 '21 at 09:34

0

The Date class isn't valid JSON. Use a date string instead.

$match: 
             { "ticket_date" : { $gte : { $date : "2020-12-24T15;15:15.000Z" }}}

edited Nov 10 '21 at 09:34

deb

6,671
2
11
27

answered Nov 08 '21 at 10:54

ghengalala

109
9

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 08 '21 at 11:19

How to use date in PDI in MongoDB input step?

1 Answers1