Hitachi Vantara Pentaho Community Forums
Results 1 to 18 of 18

Thread: processing array data in JSON output

  1. #1
    Join Date
    Jun 2012
    Posts
    29

    Post processing array data in JSON output

    In the JSON Output step I thought I should be able to do something like "$.flight.flight-engine-start" to get that field in the array, but that throws an error. I also tried "$.flight[flight-engine-start]" and a few other variations but no luck.

    Here's my JSON:


    "flight": [ { "flight-engine-start": "06:50", "flight-engine-stop": "09:30", "flight-head-airtime": "2.7", "flight-head-air-time": "0.0", "flight-head-hobbs": "", "content": [ { "flight-description": "crew change drill #1", "flight-time-up": "06:50", "flight-time-down": "07:30", "flight-airtime": "0.7", "flight-hobbs": "" }, { "flight-description": "service drill", "flight-time-up": "07:35", "flight-time-down": "08:30", "flight-airtime": "0.9", "flight-hobbs": "" },

  2. #2
    Join Date
    Sep 2011
    Posts
    152

    Default

    Can u post the error or ktr file??

    Quote Originally Posted by fly_boyz View Post
    In the JSON Output step I thought I should be able to do something like "$.flight.flight-engine-start" to get that field in the array, but that throws an error. I also tried "$.flight[flight-engine-start]" and a few other variations but no luck.

    Here's my JSON:


    "flight": [ { "flight-engine-start": "06:50", "flight-engine-stop": "09:30", "flight-head-airtime": "2.7", "flight-head-air-time": "0.0", "flight-head-hobbs": "", "content": [ { "flight-description": "crew change drill #1", "flight-time-up": "06:50", "flight-time-down": "07:30", "flight-airtime": "0.7", "flight-hobbs": "" }, { "flight-description": "service drill", "flight-time-up": "07:35", "flight-time-down": "08:30", "flight-airtime": "0.9", "flight-hobbs": "" },

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

    Default

    I have reason to believe, that you will succeed using "$..flight.flight-engine-start" as your access path.
    So long, and thanks for all the fish.

  4. #4
    Join Date
    Jun 2012
    Posts
    29

    Default

    Not much in the execution results.

    2012/06/13 19:13:07 - Pilot Report - Datawarehouse - ERROR (version 4.3.0-GA, build 16753 from 2012-04-18 21.39.30 by buildguy) : Errors detected!


    Here's a screenshot.

    Name:  Screen Shot 2012-06-13 at 7.38.53 PM.jpg
Views: 1207
Size:  15.4 KB

    I did just start playing with the latest PDI build which has a couchdb input step so that might resolve this problem (once I can get that step to work).

  5. #5
    Join Date
    Jun 2012
    Posts
    29

    Default

    tried "$..flight.flight-engine.start" as well as "$...flight.flight-engine.start" and those two didn't work either.

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

    Default

    May I introduce to you JSONPath: http://jsonpath.curiousconcept.com/

    You will find out, that your usage of "$..." is kind of outstanding.

    Try to get a single path working, before you add more fields.
    So long, and thanks for all the fish.

  7. #7
    Join Date
    Jun 2012
    Posts
    29

    Default

    THANK YOU SO MUCH! This was an obvious hole in my knowledge about JSON and this helps a lot.

  8. #8
    Join Date
    Jun 2012
    Posts
    29

    Default

    OK, it seems that something that works in JSONPath Expression Tester doesn't work in PDI.

    "$.flight[*].content[*].flight-description" doesn't work in PDI for the following JSON:

    {
    "_id": "altair.pilot-report.08bbb929df33fff065733914ed04e9b2",
    "_rev": "1-b481fa5784901c3a72ed687033e6f7a0",
    "app": "plans",
    "resource": "pilot-report",
    "date": "May 31, 2012",
    "job": "altair.job.bcdd93487f4b581c7dffa95f59e3e4d3",
    "aircraft": "altair.aircraft.bcdd93487f4b581c7dffa95f59e23421",
    "client": "altair.client.bcdd93487f4b581c7dffa95f59e3017e",
    "hobbs": "",
    "contract": "altair.contract.bcdd93487f4b581c7dffa95f59e39c21",
    "po": "",
    "bag-moves": "",
    "drill-moves": "",
    "ski-runs": "",
    "water-drops": "",
    "flight": [
    {
    "flight-engine-start": "09:00",
    "flight-engine-stop": "09:30",
    "flight-head-airtime": "0.5",
    "flight-head-air-time": "0.5",
    "flight-head-hobbs": "12000",
    "content": [
    {
    "flight-description": "Some leg here",
    "flight-time-up": "09:05",
    "flight-time-down": "09:15",
    "flight-airtime": "0.2",
    "flight-hobbs": ""
    },
    {
    "flight-description": "the next leg",
    "flight-time-up": "09:17",
    "flight-time-down": "09:25",
    "flight-airtime": "0.1",
    "flight-hobbs": ""
    }
    ]
    },
    {
    "flight-engine-start": "10:00",
    "flight-engine-stop": "11:00",
    "flight-head-airtime": "1",
    "flight-head-air-time": "1.1",
    "flight-head-hobbs": "",
    "content": [
    {
    "flight-description": "First second leg",
    "flight-time-up": "10:05",
    "flight-time-down": "10:55",
    "flight-airtime": "0.8",
    "flight-hobbs": ""
    }
    ]
    }
    ],
    "duty": [
    {
    "duty-crew": "altair.user.bcdd93487f4b581c7dffa95f59c92589",
    "duty-type": "normal",
    "duty-start": "06:00",
    "duty-end": "18:00"
    }
    ],
    "expenses": [
    {
    "expenses-crew": "altair.user.bcdd93487f4b581c7dffa95f59e62f69",
    "expenses-breakfast": "checked",
    "expenses-lunch": "checked",
    "expenses-dinner": "checked",
    "expenses-other": "something else",
    "expenses-cost": "12.00"
    }
    ],
    "fuel": [
    {
    "fuel-supplier": "Us",
    "fuel-location": "some place",
    "fuel-quantity": "112L"
    }
    ],
    "dgcarried1": "checked",
    "dgcarried10": "checked",
    "meta": {
    "createdOn": "Thu, 31 May 2012 02:39:15 GMT",
    "createdBy": "altair.user.bcdd93487f4b581c7dffa95f5981dc2a",
    "modifiedOn": null,
    "modifiedBy": null
    },
    "no-flight": "unchecked",
    "dgcarried2": "unchecked",
    "dgcarried3": "unchecked",
    "dgcarried4": "unchecked",
    "dgcarried5": "unchecked",
    "dgcarried6": "unchecked",
    "dgcarried7": "unchecked",
    "dgcarried8": "unchecked",
    "dgcarried9": "unchecked",
    "dgcarried11": "unchecked",
    "name": "Pilot report #5"
    }

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

    Default

    Quote Originally Posted by fly_boyz View Post
    "$.flight[*].content[*].flight-description" doesn't work in PDI for the following JSON ...
    Works perfectly well over here.
    So long, and thanks for all the fish.

  10. #10
    Join Date
    Jun 2012
    Posts
    29

    Default

    Strange. I ran more tests as follows:

    Currently in PDI (4.3.0) if I have this:

    {
    "id": "08bbb929df33fff065733914ed04e9b2",
    "item": [
    {
    "item1": "09:00",
    "item2": "09:30",
    "item3": [
    {
    "itema": "Something",
    "itemb": "Something"
    }
    ]
    }
    ]
    }

    This works: "$.item[*].item3[*].itemb"

    However, it does not work if I have this (JSON Input step returns an error):

    {
    "_id": "08bbb929df33fff065733914ed04e9b2",
    "item": [
    {
    "item1": "09:00",
    "item2": "09:30",
    "item3": [
    {
    "itema": "Somethinga",
    "itemb": "Somethingb"
    },
    {
    "itema": "Somethingc",
    "itemb": "Somethingd"
    }
    ]
    }
    ]
    }

    My understanding is that it should actually return "Somethingb, Somethingd"

    marabu, am I missing something obvious?

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

    Default

    Same again, works for me.
    Not sure what you're missing.

    What's that error you keep talking about?
    My fortune teller took a day off...
    Last edited by marabu; 06-14-2012 at 06:53 AM.
    So long, and thanks for all the fish.

  12. #12
    Join Date
    Sep 2011
    Posts
    152

    Default

    Ya its working for me... The same data whatever u have posted it here its working fine...



    Quote Originally Posted by fly_boyz View Post
    Strange. I ran more tests as follows:

    Currently in PDI (4.3.0) if I have this:

    {
    "id": "08bbb929df33fff065733914ed04e9b2",
    "item": [
    {
    "item1": "09:00",
    "item2": "09:30",
    "item3": [
    {
    "itema": "Something",
    "itemb": "Something"
    }
    ]
    }
    ]
    }

    This works: "$.item[*].item3[*].itemb"

    However, it does not work if I have this (JSON Input step returns an error):

    {
    "_id": "08bbb929df33fff065733914ed04e9b2",
    "item": [
    {
    "item1": "09:00",
    "item2": "09:30",
    "item3": [
    {
    "itema": "Somethinga",
    "itemb": "Somethingb"
    },
    {
    "itema": "Somethingc",
    "itemb": "Somethingd"
    }
    ]
    }
    ]
    }

    My understanding is that it should actually return "Somethingb, Somethingd"

    marabu, am I missing something obvious?

  13. #13
    Join Date
    Jun 2012
    Posts
    29

    Default

    [edited for clairity with changes to the .ktr and error message]

    Well, I'm stumped. I've attached the .ktr. Here's the error. Thanks for the help, really appreciate it.

    2012/06/14 20:39:40 - Json Input.0 - The field [json] contain [{ "_id": "08bbb929df33fff065733914ed04e9b2", "item": [{"item1": "09:00", "item2": "09:30", "item3": [{ "itema": "Somethinga", "itemb": "Somethingb"}, {"itema": "Somethingc", "itemb": "Somethingd"} ] } ] }]
    2012/06/14 20:39:40 - Json Input.0 - ERROR (version 4.3.0-GA, build 16753 from 2012-04-18 21.39.30 by buildguy) : Unexpected Error : org.pentaho.di.core.exception.KettleException:
    2012/06/14 20:39:40 - Json Input.0 - ERROR (version 4.3.0-GA, build 16753 from 2012-04-18 21.39.30 by buildguy) : The data structure is not the same inside the resource! We found 2 values for json path [$.item[*].item3[*].itemb], which is different that the number retourned for path [$._id] (1 values). We MUST have the same number of values for all paths.
    2012/06/14 20:39:40 - Json Input.0 - ERROR (version 4.3.0-GA, build 16753 from 2012-04-18 21.39.30 by buildguy) : org.pentaho.di.core.exception.KettleException:
    2012/06/14 20:39:40 - Json Input.0 - ERROR (version 4.3.0-GA, build 16753 from 2012-04-18 21.39.30 by buildguy) : The data structure is not the same inside the resource! We found 2 values for json path [$.item[*].item3[*].itemb], which is different that the number retourned for path [$._id] (1 values). We MUST have the same number of values for all paths.
    2012/06/14 20:39:40 - Json Input.0 - ERROR (version 4.3.0-GA, build 16753 from 2012-04-18 21.39.30 by buildguy) :
    2012/06/14 20:39:40 - Json Input.0 - ERROR (version 4.3.0-GA, build 16753 from 2012-04-18 21.39.30 by buildguy) : at org.pentaho.di.trans.steps.jsoninput.JsonInput.parseJson(JsonInput.java:282)
    2012/06/14 20:39:40 - Json Input.0 - ERROR (version 4.3.0-GA, build 16753 from 2012-04-18 21.39.30 by buildguy) : at org.pentaho.di.trans.steps.jsoninput.JsonInput.readFileOrString(JsonInput.java:258)
    2012/06/14 20:39:40 - Json Input.0 - ERROR (version 4.3.0-GA, build 16753 from 2012-04-18 21.39.30 by buildguy) : at org.pentaho.di.trans.steps.jsoninput.JsonInput.ReadNextString(JsonInput.java:174)
    2012/06/14 20:39:40 - Json Input.0 - ERROR (version 4.3.0-GA, build 16753 from 2012-04-18 21.39.30 by buildguy) : at org.pentaho.di.trans.steps.jsoninput.JsonInput.getOneRow(JsonInput.java:399)
    2012/06/14 20:39:40 - Json Input.0 - ERROR (version 4.3.0-GA, build 16753 from 2012-04-18 21.39.30 by buildguy) : at org.pentaho.di.trans.steps.jsoninput.JsonInput.processRow(JsonInput.java:349)
    2012/06/14 20:39:40 - Json Input.0 - ERROR (version 4.3.0-GA, build 16753 from 2012-04-18 21.39.30 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
    2012/06/14 20:39:40 - Json Input.0 - ERROR (version 4.3.0-GA, build 16753 from 2012-04-18 21.39.30 by buildguy) : at java.lang.Thread.run(Thread.java:680)
    2012/06/14 20:39:40 - Json Input.0 - Signaling 'output done' to 1 output rowsets.

    test transform.ktr
    Last edited by fly_boyz; 06-14-2012 at 08:42 AM.

  14. #14
    Join Date
    Jun 2012
    Posts
    29

    Default

    hum...I see the problem now. Only solution to break the JSON input step into separate steps I guess?

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

    Default

    It's a bit tricky, but we must overcome the ways of the "JSON Input" step somehow.
    There's room for improvement, though.

    test transform.ktr
    So long, and thanks for all the fish.

  16. #16
    Join Date
    May 2011
    Posts
    1

    Default

    I had the same exact issue. I used two json steps one after another to be able to parse the array data. I think it is a bug. it simply does not parse nested json arrays.

  17. #17
    Join Date
    Jan 2014
    Posts
    6

    Default

    Hello All,

    Could you please someone change the title of the thread. It says Json Ouput, but talking about Json Input.

    Thanks,
    Vijay

  18. #18
    Join Date
    Nov 2016
    Posts
    3

    Post

    I also had this issue. Thanks to this thread I was able to solve by passing the array to a second JSON step and then to a select step to create the data in the table.

Tags for this Thread

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.