extraction only values from key-value pairs from json file without manually specifying the keys as column

[mohd_amzad9559]

Hi,

I have a json file where data looks like below

{

"col1" : {

"0" : 700,

"1" : 750,

"2" : 720,

"4" : 730,

}

"col2" : {

"0" : 800,

"1" : 850,

"2" : 820,

"4" : 830,

}

"col3" : {

"0" : 900,

"1" : 950,

"2" : 920,

"4" : 930,

}

}

I want the data in my snowflake table as below:

col1 col2 col3

700 800 900   

750 850 950   

720 820 920   

730 830 930   

Note :- In my original file it have more than 800 key pairs for each node, hence we can not specify key value in jsonpath in tFileInputDelimited,

we need to do it manually

Thanks in Advance

[quentin-vigne]

How many columns do you have ? If only 3-4 then follow this : 

You have to use the loop option in the JSON input if you have that many key pairs.

Use tFileInputJSON, there is a Json loop option where you will put : "$..col1.*"

And do as many tFileInputJSON as you have columns and then join them back together later in the job

[Sonal_Reddy_18]

Is number of columns a fixed value?

[mohd_amzad9559 · Accepted solution]

I have done it using tjavaflex, below code is working for me:

// start part of your Java code

String str = "your json content"; --this should be your json file content 

  JSONObject jobj = new JSONObject(str);  

JSONObject col1  = jobj.getJSONObject("col1");

JSONObject col2  = jobj.getJSONObject("col2");

JSONObject col3  = jobj.getJSONObject("col3");

for (String key : col1.keySet()){

  // here is the main part of the component,

// a piece of code executed in the row

// loop

Int value1 =col1.getInt(key);

row7.col1 = value1;

Int value2 =col2.getInt(key);

row7.col2 = value2;

Int value3 =col3.getInt(key);

row7.col3 = value3;

// end of the component, outside/closing the loop

    }