Hitachi Vantara Pentaho Community Forums
Results 1 to 12 of 12

Thread: JSON Path help required for Pentaho DI CE 6.0

  1. #1
    Join Date
    Mar 2016
    Posts
    8

    Default JSON Path help required for Pentaho DI CE 6.0

    Hi,

    Could any of you please help me how to get the top object value in PentahoDI. I have got the other elements like Category, Subcategory, section from the following example of Json file. However, I need to capture the first root object which is x@chapter@e50de0196d77495d9b50fc05567b4a4b and x@e50de0196d77495d9b50fc05567b4a4b

    {
    "x@chapter@e50de0196d77495d9b50fc05567b4a4b": {
    "Category": "chapter",
    "SubCategory": [
    "x@4eb9072cf36f4d6fa1e98717e6bb54f7",
    "x@d85849fbde324690b6067f3b18c4258d",
    "x@3edff1a1864f41fe8b212df2bc96bf13"
    ],
    "Section": {
    "display_name": "Week 1 Section"
    }
    },
    "x@e50de0196d77495d9b50fc05567b4a4b": {
    "category": "course",
    "Subcategory": [
    "x@e50de0196d77495d9b50fc05567b4a4b"
    ],
    "Section": {
    "advanced_modules": [
    "google-document"
    ],
    }
    }
    }

    In the Fields tab of the Json Input step I have given the Names and Paths as : Category --> $..Category, Subcategory --> $..Subcategory, Section --> $..Section.

    However, I am unable to get the root element as it is crucial information for us to work on it. ex(x@chapter@e50de0196d77495d9b50fc05567b4a4b and x@e50de0196d77495d9b50fc05567b4a4b)

    Could somebody please help?

    Thanks

    Dubbu

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

    Default

    JSONPath (in the JSON-Input step) is for retrieving values, not keys.
    Last edited by marabu; 03-25-2016 at 08:40 AM. Reason: clarification in parentheses
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Mar 2016
    Posts
    8

    Default

    Hi Marabu,

    Thank you for your reply. Is there anyway I can grab this key value in JsonInput step of Pentaho DI CE (6.0) as this information is very crucial.


    Dubbu

  4. #4
    Join Date
    Aug 2011
    Posts
    360

    Default

    Hi you ll have to use javascript for that. Get your json in a string, then in javascript:
    var obj = JSON.parse (jsonstring);
    var keys = Object.keys ( obj)

  5. #5
    Join Date
    Mar 2016
    Posts
    8

    Default

    Hi Mathias,

    Thank you for your reply.

    I wasn't working on this for the last couple of days and so I didnt come back to you on the result.

    I have tried to create a MJSV step and did the code but unfortunately still it doesnt work and throwing an error saying org.mozilla.javascript.UniqueTag@795cfbae: NOT_FOUND.

    Please find attached the screenshot in which I have shown the script step and the output as well. I am expecting a value of x@chapter@e50de0196d77495d9b50fc05567b4a4b and x@e50de0196d77495d9b50fc05567b4a4b, but the keys are showing "metadata,children,category" only which is Category, Subcategory and Section as mentioned in the following thread.

    Name:  JscriptError.jpg
Views: 204
Size:  26.4 KB

  6. #6
    Join Date
    Jun 2013
    Posts
    24

    Default

    The forum resizes images, so it's really hard to read much, but it looks like you might have a JSON reader after your MJSV? You don't need that, if you do, because your MJSV already converted it to an object, and then you got the values. In the MJSV, you'll also need to create additional rows for each key/value in your JSON, and use a method like this one to output all of your new rows, but not the input row.

  7. #7
    Join Date
    Mar 2016
    Posts
    8

    Default

    HI JoBrad,

    Thank you for your reply.

    I have tried it with the code which generate rows and the script but when I try to compile the script it throws the following error:

    2016/04/01 16:26:18 - Modified Java Script Value.0 - Caused by: org.mozilla.javascript.EcmaError: TypeError: Cannot find function propertyNames in object [object Object]. (script#66)

    I went thru many searches in many forums but I am unable to find any solution to retrieve this Unnamed element in the JSON file which is very crucial bit for me to proceed.

    I would like to mention an example here again maybe in a simpler way.

    {
    "object1" {
    "Category": "chapter",
    "SubCategory": [ "Subcategoryvalues1", "subcategoryvalues2"],
    "Section": {"display_name": "Week 1 Section"}},
    "object2": {
    "category": "course",
    "Subcategory": ["subcategory1"],
    "Section": {"advanced_modules": ["google-document"], }}
    }



    This is how exactly my Json files arrives and I am populating into the table. Because of the object1 and object2 created dynamically I am unable to fetch the value. I can get the values of category, subcategory and section by giving the path $..category, $.subcategory and $.section to get those values.

    How do I get the Pentaho or Javascript to fetch the value Object1 and object2 into some variables /columns.

    I am really struggling to get any output thou I have spend huge amount of time working on this.

    Could you please help?

    I had tried what the fellow members in this board has mentioned but I am unable to get any values and the error message is similar to what I have mentioned above.


    BTW I dont have any JSON Input after this code as I am still waiting for some solution so that I can populate in MySQL.

    Thanks and look forward to get some help.

    Dubbu
    Last edited by Dubbu; 04-01-2016 at 11:41 AM.

  8. #8
    Join Date
    Jun 2013
    Posts
    24

    Default

    Can you paste your MJSV code?

  9. #9
    Join Date
    Mar 2016
    Posts
    8

    Default

    Hi JoBrad,

    Thank you for coming back to me.

    I have literally used the code from the javascript which you have mentioned.

    --------------------------------------------------------------------------
    var props = JSON.parse (JBlock);

    var e = props.propertyNames();

    while (e.hasMoreElements()) {

    var propName = e.nextElement();
    var propValue = props.get(propName);

    var row = createRowCopy(getOutputRowMeta().size());

    var idx = getInputRowMeta().size();

    row[idx++] = propName;
    row[idx++] = propValue;

    putRow(row);
    }

    -------------------------------------------------------------------

    And in the output of the MJSV propName and propValue are the fields as String type. My intention is just to see which object holds this value so that I can use that variable/object as an output.

    Thanks,

    Dubbu

  10. #10
    Join Date
    Jun 2013
    Posts
    24

    Default

    There are a few apparent problems here:
    * It seems like you've literally copy/pasted the example code. But while that code fits your general use-case, it wasn't made for your transformation, so that is a big no-no. What is the name of the field that has your JSON in it? That name should replace jsonstring, in this line.
    Code:
    var obj = JSON.parse (jsonstring);
    * Secondly, JSON.parse returns a simple object, that doesn't have any properties that weren't defined in your JSON (other than the standard Object properties), while Slawomir's example returns a Properties object that has a method called propertyNames. So, you can't use the propertyNames method to get the names. You'll need to use Matthias' example to get the keys of this object.

    Code:
    var keys = Object.keys(obj);
    Then you need to loop through these keys, and extract their values from the obj variable. Google is your friend here (looping through object properties), but the Mozilla Developer Network is an excellent resource for understanding Javascript (and other technologies), and I highly recommend them.

  11. #11
    Join Date
    Mar 2016
    Posts
    8

    Default

    Hi,

    Thank you for your reply.

    I had done already exactly what you have said above but with no luck. I have followed as what Matthias example but I managed to get the Keys value (ie. Category, Subcategory and section) but not OBJECT.

    I am unable to find any resources which explains me to get the names of the object.

    If you come across any please let me know

    Thanks

    Dubbu

  12. #12
    Join Date
    Jun 2013
    Posts
    24

    Default

    Dubbu,
    I recently had to handle a similar situation, and was a little frustrated, frankly, that Spoon deals with XML far better than it does JSON. I've attached a transformation that will turn a JSON string that has arbitrary properties into one with defined properties. You will need to use multiple JSON Input steps for this method, but at least it will let you access them using JSON Path.
    Parse JSON.zip

    I would highly recommend learning Javascript, if you are going to do this kind of work. I probably wouldn't be able to help you had I not recently done this, and I can't offer any sort of warranty for future issues, of course

    I would also make sure that the JSON strings you're working with have been validated. All of the strings you posted here had issues. JSONLint is a great site for this.

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.