Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: MongoDB datetime Issue

  1. #1
    Join Date
    Feb 2017
    Posts
    8

    Default MongoDB datetime Issue

    Hi Guys,

    As a new user to DI, I'm having a bit of an issue trying to pull in the datetime field from a mongodb dataset. Here's what the dataset looks like straight from mongo:

    { "_id" : ObjectId("574fa0db018b046"), "assoc_time" : NumberLong(1464834747), "disassoc_time" : NumberLong(1464835984), "duration" : NumberLong(1237), "tx_bytes" : NumberLong(9784580), "rx_bytes" : NumberLong(9411424), "bytes" : NumberLong(19196004), "user_id" : ObjectId("574fa0db018b046"), "mac" : "aa:bb:cc:dd:ee:ff:00", "name" : null, "hostname" : "testing", "is_guest" : false, "site_id" : "574fa0db018b046", "ip" : "192.168.1.2", "is_wired" : false, "ap_mac" : aa:bb:cc:ee:ff:00", "radio" : "na", "channel" : 161, "roam_count" : 1, "o" : "session", "session" : "574fa0db018b046", "time" : NumberLong("1464834747000"), "datetime" : ISODate("2016-06-02T02:32:27Z") }

    Although, when I'm pulling in the data from Mongo using the JSON input step, I'm seeing the following fields.

    { "_id" : { "$oid" : "574fa0db018b046"} , "assoc_time" : 1483323280 , "disassoc_time" : 1483323553 , "duration" : 273 , "tx_bytes" : 1045933 , "rx_bytes" : 1226784 , "bytes" : 2272717 , "user_id" : { "$oid" : "574fa0db018b046"} , "mac" : "aa:bb:cc:dd:ee:ff:00" , "name" : null , "hostname" : "testing" , "is_guest" : false , "site_id" : "574fa0db018b046" , "ip" : "192.168.1.2" , "is_wired" : false , "ap_mac" : "aa:bb:cc:dd:ee:ff:00" , "radio" : "na" , "channel" : 100 , "roam_count" : 0 , "o" : "session" , "session" : "574fa0db018b046" , "time" : 1483323280000 , "datetime" : { "$date" : "2017-01-02T02:14:40.000Z"}}

    I'm not sure if this is significant or not, but the datetime field changed from this:

    "datetime" : ISODate("2016-06-02T02:32:27Z") to this--> "datetime" : { "$date" : "2017-01-02T02:14:40.000Z"}}

    In any regard, on the fields tab of the JSON Input, I'm just can't seem to nail down how to pull in that datetime field seemingly from an error in the format column. Using datetime in the name column, date in the path column with type set as date seems to at least get me read errors when trying to view the transformation. I've tried custom format like this --> yyyy-MM-dd'T'HH:mm:ss'.000'Z with no luck. Any hints?

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Using JSON-Input and your second sample I have no problem reading datetime as $.datetime.['$date'] as a Date field with format yyyy-MM-dd'T'HH:mm:ss.SSSX

    What was your problem again?
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Feb 2017
    Posts
    8

    Default

    Quote Originally Posted by marabu View Post
    Using JSON-Input and your second sample I have no problem reading datetime as $.datetime.['$date'] as a Date field with format yyyy-MM-dd'T'HH:mm:ss.SSSX

    What was your problem again?
    Is this how you have your fields setup? I get no results when entered as shown and as what I think you're describing. Thanks for your help!

    Name:  datetime1.jpg
Views: 353
Size:  28.3 KBName:  datetime2.jpg
Views: 336
Size:  33.4 KB

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Your JSONPath expression looks so much simpler than mine.
    Also, you should state your Java and Kettle version.
    The X DateFormat pattern character isn't supported before Java 7, I believe.
    And before Kettle 7 there were some restrictions to the JSONPath syntax.
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Feb 2017
    Posts
    8

    Default

    Quote Originally Posted by marabu View Post
    Your JSONPath expression looks so much simpler than mine.
    Also, you should state your Java and Kettle version.
    The X DateFormat pattern character isn't supported before Java 7, I believe.
    And before Kettle 7 there were some restrictions to the JSONPath syntax.
    What do you mean by simpler than mine? I'm running the community edition. Is that the difference? Or are you saying that I should be entering "$.datetime.['$date']" in another way based on my my screenshot?

    Running:

    Ubuntu 16.04
    Oracle Java 8 update 121
    Data Integration v7.0.0.0.25 community edition

  6. #6
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    You definitely should enter my slightly more complex JSONPath expression, if your input looks like what you posted.
    So long, and thanks for all the fish.

  7. #7
    Join Date
    Feb 2017
    Posts
    8

    Default

    Quote Originally Posted by marabu View Post
    You definitely should enter my slightly more complex JSONPath expression, if your input looks like what you posted.
    Where exactly do I enter your expression at? I've tried a couple variations with no success.

    Name:  datetime1.jpg
Views: 337
Size:  16.4 KBName:  datetime2.jpg
Views: 324
Size:  43.4 KB

  8. #8
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Wait, you don't know JSONPath at all? Read about it.
    You should have used copy-and-paste to enter my expression.
    Somehow you lost a significant part of it: There was a dot following the root symbol.
    So long, and thanks for all the fish.

  9. #9
    Join Date
    Feb 2017
    Posts
    8

    Default

    Oh man, I appreciate the help. I can't believe I fibbed the entry of that. I think I had been looking at my screen too long! That seemed to pull the dates out when entered correctly. Thanks again!

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.