Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: MongoDB input with timestamp column

  1. #1
    Join Date
    Aug 2017
    Posts
    1

    Default MongoDB input with timestamp column

    Hi,

    I'm trying to run the following query using a mongoDB input step:
    Code:
    db.oplog.rs.aggregate(
    [
    { $match: { 
      op: {$in: ['i', 'u','d']},
      ns: "dbName.collectionName",
      ts: {$gt: Timestamp(ISODate("2017-10-09T00:00:00.0Z").getTime() / 1000, 0)}
      }
    },
    { $project: {
      id: {$cond: [{$eq: ["$op", 'u']}, "$o2._id", "$o._id"]},
      ts: "$ts",
      op: "$op",
      ns: "$ns"
      }
    },
    ]);
    I recieve the error JSONParseException, Although the query runs without promblems on mongodb shell.

    the idea is to use mongodb oplog collection to track changes in documents for incremental ETL. I want to select documents with ts date greater or equal to my last ETL run date (currenlty I use an hardcoded date, next I'll put a variable there), the problem is, ts is a timestamp and not a date so I need to create a timestamp object somehow.

    I found that I need to use
    Code:
    {"$date": "2012-01-01T15:00:00.000Z"}
    instead of ISODate, but can't find a solutoin for Timestamp function.

    Any suggestions on how I can implement this in PDI?

  2. #2
    Join Date
    Apr 2014
    Posts
    18

    Default

    Try this

    https://forums.pentaho.com/showthrea...ighlight=mongo

    Quote Originally Posted by boriskard View Post
    Hi,

    I'm trying to run the following query using a mongoDB input step:
    Code:
    db.oplog.rs.aggregate(
    [
    { $match: { 
      op: {$in: ['i', 'u','d']},
      ns: "dbName.collectionName",
      ts: {$gt: Timestamp(ISODate("2017-10-09T00:00:00.0Z").getTime() / 1000, 0)}
      }
    },
    { $project: {
      id: {$cond: [{$eq: ["$op", 'u']}, "$o2._id", "$o._id"]},
      ts: "$ts",
      op: "$op",
      ns: "$ns"
      }
    },
    ]);
    I recieve the error JSONParseException, Although the query runs without promblems on mongodb shell.

    the idea is to use mongodb oplog collection to track changes in documents for incremental ETL. I want to select documents with ts date greater or equal to my last ETL run date (currenlty I use an hardcoded date, next I'll put a variable there), the problem is, ts is a timestamp and not a date so I need to create a timestamp object somehow.

    I found that I need to use
    Code:
    {"$date": "2012-01-01T15:00:00.000Z"}
    instead of ISODate, but can't find a solutoin for Timestamp function.

    Any suggestions on how I can implement this in PDI?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.