Pentaho Data Integration : JSON input Step

JSON (JavaScript Object Notation) is a text based, light weight data inter change format.
This format enjoys a wide availability of implementations and is platform independent.

Stepwise illustration on the usage of Pentaho JSON step given below.

Step 1


Create sample transformation with JSON input step.


Step 2

Browse and add sample test file in JSON format.

Sample JSON Input data file :
Real time stock quotes in JSON format used as sample test input for demo purpose.
{"stockdata":[

{"DAY_LOW":547.81,"BID":551.08,"OPEN":550.88,"52WK_LOW":385.1,"LAST_TRADE":551.08,"DAY_HIGH":557.29,"STOCK_NAME":"APPLE INC",
"RUN_DATE":"2014-01-22 12:10:04.000000000","VOLUME":11654341,"STOCK_SYMBOL":"AAPL","52WK_HIGH":575.14,"ASK":551.17},

{"DAY_LOW":1158.86,"BID":1162.3,"OPEN":1166.6,"52WK_LOW":735.79,"LAST_TRADE":1162.54,"DAY_HIGH":1167.89,"STOCK_NAME":"GOOGLE INC","RUN_DATE":"2014-01-22 12:10:04.000000000","VOLUME":1064205,"STOCK_SYMBOL":"GOOG","52WK_HIGH":1164.0,"ASK":1162.62},

{"DAY_LOW":57.1,"BID":57.26,"OPEN":58.8,"52WK_LOW":22.67,"LAST_TRADE":57.26,"DAY_HIGH":59.31,"STOCK_NAME":"FACEBOOK",

"RUN_DATE":"2014-01-22 12:10:05.000000000","VOLUME":52050684,"STOCK_SYMBOL":"FB","52WK_HIGH":58.96,"ASK":57.27},
{"DAY_LOW":39.32,"BID":40.06,"OPEN":39.66,"52WK_LOW":19.31,"LAST_TRADE":40.06,"DAY_HIGH":40.13,"STOCK_NAME":"YAHOO",
"RUN_DATE":"2014-01-22 12:10:05.000000000","VOLUME":8438596,"STOCK_SYMBOL":"YHOO","52WK_HIGH":41.72,"ASK":40.07},
{"DAY_LOW":35.75,"BID":36.07,"OPEN":36.26,"52WK_LOW":27.1,"LAST_TRADE":36.08,"DAY_HIGH":36.32,"STOCK_NAME":"MICROSOFT",
"RUN_DATE":"2014-01-22 12:10:05.000000000","VOLUME":14150161,"STOCK_SYMBOL":"MSFT","52WK_HIGH":38.98,"ASK":36.08}
]}

Step 3

Configure the field names and paths.
This can vary based requirements.

For example, individual field values can be retrieved using following paths.

STOCK_SYMBOL --> $..STOCK_SYMBOL
STOCK_NAME --> $..STOCK_NAME

All records can be retrieved by specifying "*" wild card in the path.
eg : $.stockdata.*


Step 4

Preview source data.

For Individual fields


For all records

9 comments :

Unknown said...

i have json file like this, where type is the column and content is the value of the column, ho i can transform.

[
{
"type": "compressed_part_number",
"content": "TSW13208GSRA"
},
{
"type": "part_id",
"content": "16313698"
},
{
"type": "manufacturer",
"content": "SAMTEC"
},
{
"type": "sentenceblock",
"content": "Easily mount this TSW-132-08-G-S-RA header connector from Samtec onto your PCB project."
}
]

Unknown said...

i have json file like this, where type is the column and content is the value of the column, so how i can transform?

Unknown said...

i have json file like this, where type is the column and content is the value of the column, so how i can transform?

Mike said...

The $.. structure is not working for me. I get a row with ALL of the data in each row. It would be as if all of the JSON response for stock data was written on each record. Any ideas on how to overcome that?

Mike said...

Nevermind it actually did work, but in the preview rows, it was very confusing. If anyone else runs into this, select the rows and put them in a csv file... it works!

Sarang Manjrekar said...

I am using the variable path as ($..X..Y..Z) , for i intend to pick values from path X/Y/Z , but instead I also get the values from all underlying paths where folder Z is present, such as (X/Y/1/Z), (X/Y/2/Z), (X/Y/3/B/Z).

What should I do to get just the desired value and not any extra value ?

ERP said...

ERPTREE Offering Oracle fusion HCM online training, Oracle Fusion SCM Online Training, Oracle fusion financials online
training, Oracle fusion hcm training, Oracle fusion scm training, Oracle fusion financials training,
Oracle fusion dba online training in Hyderabad, Bangalore,Gurgon, Noida, India, Dubai, UAE, USA, Kuwait, UK, Singapore, Saudi Arabia, Canada,
Delhi, Chennai, Kolkata, Pune, Mumbai, Ahmedabad.


Oracle fusion HCM Online Training

Oracle Fusion HCM Training

for IT the said...

great

Tanika Co Valda said...

Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a Front end developer learn from Javascript Training in Chennai . or learn thru Javascript Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry. JavaScript Training in Chennai

Post a Comment

UA-46724997-1