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

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

mohd_amzad9559 — Tue, 03 Dec 2024 07:43:13 GMT

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

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

quentin-vigne — Thu, 05 Dec 2024 10:03:50 GMT

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

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

Sonal_Reddy_18 — Thu, 05 Dec 2024 20:43:13 GMT

Is number of columns a fixed value?

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

mohd_amzad9559 — Fri, 06 Dec 2024 06:01:18 GMT

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

}