Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Kette - Query by date in MongoDB input

  1. #1
    Join Date
    Nov 2015
    Posts
    11

    Default Kette - Query by date in MongoDB input

    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
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by sourav11b View Post
    then using this variable in the query parameter of MongoDB input step
    Are you setting and using a variable in the same transformation?
    That's not allowed. It simply won't work.

  3. #3
    jdixon Guest

    Default

    What value(s) does dt end up with? It looks like you are missing the 'T' from the middle.
    I would also check the format for the variable substitution in the MongoDB input step. Shouldn't it be "${dt}"?

  4. #4
    Join Date
    Nov 2015
    Posts
    11

    Default

    2025-11-27T11:11:24:484Z - this is value i am getting after applying date2str(LMD, "yyyy'-'MM'-'dd'T'HH':'mm':'ss':'SSS'Z'");

    Quote Originally Posted by jdixon View Post
    What value(s) does dt end up with? It looks like you are missing the 'T' from the middle.
    I would also check the format for the variable substitution in the MongoDB input step. Shouldn't it be "${dt}"?

  5. #5
    Join Date
    Nov 2015
    Posts
    11

    Default

    an i have also used ${dt} in place of $dt but still did not help.

    Quote Originally Posted by jdixon View Post
    What value(s) does dt end up with? It looks like you are missing the 'T' from the middle.
    I would also check the format for the variable substitution in the MongoDB input step. Shouldn't it be "${dt}"?

  6. #6
    Join Date
    Nov 2015
    Posts
    11

    Default

    so my requirement is to query a database table (oracle) to check last run time. Then based on that time run a mongodb input with date query so that i pick rows only after that timestamp. Any suggestions how to achieve this if i cannot set the variables in same transformation - should i split it up into two and run a job?

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

    Default

    Quote Originally Posted by sourav11b View Post
    Any suggestions how to achieve this if i cannot set the variables in same transformation - should i split it up into two and run a job?
    I read http://wiki.pentaho.com/display/EAI/MongoDB+Input , and I don't see anything suggesting that the MongoDB Input supports field substitution into the query.
    So you will likely need to use variables or parameters.
    I would break the transform into two parts, use a job, and use parameters rather than variables.

  8. #8
    Join Date
    Nov 2015
    Posts
    11

    Default

    Do you have any demo job and transformation so that i can skip the learning curve and cook up a quick demo .The documentation is not helping much and i need to show some results quick . Any help would be highly appreciated.

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.