Pentaho: Getting only one in row from JSON input file

Question

I am getting one JSON file from SFTP and trying it to insert into oracle but in the preview section i am getting only one row and only one row inserting into table. I already tried to modify to number to preview rows to 10000 but nothing working out.

{"postal_code":"XX","build_id":"XX","categories":[],"closed":false,"closed_reasons":[],"email":["XX"],"external_link":{"facebook":[],"yelp":[""]},"hq":false,"location":{"lat":xxxxx,"lon":xxxxx},"metro":"Cxxxxx, IL","naics_codes":[{"category_code":"XX","category_description":"XX"},{"category_code":"XX","category_description":"xxxxx "},{"category_code":"XX","category_description":"xxxxx "},{"category_code":xxxxx","category_description":"XX"}],"name":"XX","place_id":"XX","place_ids":["xxx","xx"],"sic_codes":[{"category_code":"XX","category_description":"XX"},{"category_code":"XX","category_description":"XX"}]}

On the `JSON Input` / `Field` tab, there is a `Repeat` column. — AlainD, Dec 01 '17 at 09:15

score 0 · Answer 1 · answered Dec 26 '17 at 17:01

Your example json is:

{
"postal_code":"XX",
"build_id":"XX",
"categories":[],
"closed":false,
"closed_reasons":[],
"email":["XX"],
"external_link":{
    "facebook":[],
    "yelp":[""]
},
"hq":false,
"location":{
    "lat":xxxxx,
    "lon":xxxxx
},
"metro":"Cxxxxx, IL",
"naics_codes":[
    {
        "category_code":"XX",
        "category_description":"XX"
    },
    {
        "category_code":"XX",
        "category_description":"xxxxx "
    },
    {
        "category_code":"XX",
        "category_description":"xxxxx "
    },
    {
        "category_code":xxxxx",
        "category_description":"XX"
    }
],
"name":"XX",
"place_id":"XX",
"place_ids":["xxx","xx"],
"sic_codes":[
    {
        "category_code":"XX",
        "category_description":"XX"
    },
    {
        "category_code":"XX",
        "category_description":"XX"
    }
]

}

So, if that's the total values in the response you're getting from the SFTP server, Pentaho is behaving well, because it's only one record. If you want PDI to recognize the fields inside that json, and split it's content, you need to specify the path to each field on the "Path" field available on the "Fields" tab on the "Json Input" step.

score 0 · Answer 2 · answered Feb 26 '19 at 11:47

0

Try using the input json file as follows:

{
    "data" : [
                {"key":"val","key":"val"},
                {"key":"val","key":"val"},
                {"key":"val","key":"val"},
                {"id":"666","name":"jnit"}
            ]
}

answered Feb 26 '19 at 11:47

KayV

12,987
11
98
148

Pentaho: Getting only one in row from JSON input file

2 Answers2