Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Kettle - Query on date field in MongoDB input query tab

  1. #1
    Join Date
    Nov 2015
    Posts
    11

    Default Kettle - Query on date field in MongoDB input query tab

    I have found multiple posts with this issue but found no real solution. So asking the question again

    so i am getting a date output field from a input table - LMD.

    i am converting the Date to string format using modified JS

    var dt = date2str(LMD, "yyyy'-'MM'-'dd HH':'mm':'ss'Z'");.

    and then using this variable in the query parameter of MongoDB input step

    { LMD : { $gte : { $date : "$dt" } } }

    does not work. But it works if i hardcode the value.

    { LMD : { $gte : { $date : "2014-12-31T00:00:00.000Z" } } }
    Am i missing something here?

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

    Default

    ${dt} would be a Kettle variable reference in your MongoDB query - $dt is something else ...
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Nov 2015
    Posts
    11

    Default

    i tried { LMD : { $gte : { $date : "${dt}" } } }.

    Still not working.

    This is the format the date field is stored in mongodb "LMD" : ISODate("2015-11-22T04:00:00.000+0000"),
    Attached Files Attached Files

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

    Default

    One thing is to use the right syntax for variable references.
    Another thing is to use the right format for the date value.
    I would try "yyyy-MM-dd'T'HH:mm:ssZ" and I would use a Select-Values step (page Meta-data) instead of JavaScript.
    I can't try - no MongoDB installed right now.
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    As mentioned in your other thread, you cannot set and use a variable in the same transformation.
    Generally, starting multiple threads on the same subject will just add confusion...

  6. #6
    Join Date
    Nov 2015
    Posts
    11

    Default

    Sorry for the confusion. Am switching this to the other thread.

    Quote Originally Posted by gutlez View Post
    As mentioned in your other thread, you cannot set and use a variable in the same transformation.
    Generally, starting multiple threads on the same subject will just add confusion...

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.