Pentaho

 View Only

 Parse JSON through JSON Input Step dynamically

  • Pentaho
  • Kettle
  • Pentaho
  • Pentaho Data Integration PDI
Ayan Jana's profile image
Ayan Jana posted 02-18-2019 06:17

Hi Team,

I am creating a generic transformation with Pentaho which would call any API and load data into RDBMS table. The transformation consists of Generate Rows, Rest Client, Json Input and Table Output.

So far so good. Now what I really want is to dynamically pass the name of the fields and Json path at runtime instead of entering them manually in Json Input Step. Because, if a different API is called at some other point of time, which would have a different structure, I dont have to manually edit the fields names and Json paths. I can directly pass them during the runtime.

I tried with lot of options along with ETL Metadata Injenction but wasnt able to succeed. Can someone from the team guide me through the right path  so that I can built a generic transformation  which would be able to parse json irrespective of the json structure ?

I am using PDI 7.1


#Pentaho
#Kettle
#PentahoDataIntegrationPDI
Brandon Jackson's profile image
Brandon Jackson posted 07-09-2020 17:53

You are presenting a lot of different kinds of issues. V7.1, Meta Injection, JSON Input. We would need to know more about where you are having problems and how you approached this. Clearly, you will have to have something handling the dynamic side of the environment, and then injecting field names and json path, type etc into the JSON Input step. The problem is going to come when your JSON has nesting.

You may have to get really creative and have JSON Inputs and switch case statements to deal with it.

 

Another approach might be to use a Javascript step and parse that JSON ball into an object and loop through it, essentially flattening it. Then read that in elsewhere and process it more. Kills your ability to do JSONpath, but makes things more dynamic. It just depends how nasty your JSON gets.

 

Related Content