processing array data in JSON output

[marabu]

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...

[rajeshbcrec]

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

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?

[fly_boyz]

[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

[fly_boyz]

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

[marabu]

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

[mdd2010]

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.