From Thursday, July, 9, 3:00 PM Pacific,
our Community site will be in
read-only mode
through Sunday, July 12th.
Thank you for your patience.

tFileInputJSON access iterating key value

Highlighted
Five Stars

tFileInputJSON access iterating key value

Hi there,

 

I've a problem transforming some JSON data. My job is as follow:

tFile -> tFileInputJSON -> ....

 

My JSON data are made that way:

{"Alice": [{"A": 1234, "B": 9876}, {..}], "Bob": [{..}, {..}, {..}]}

 

I managed to iterate through all the attributes ('A', 'B' for instance with "$.*[*]" set as loop json query and "@.A") but I didn't find a way to retrieve the value of the key in the JSON path. I want to retrieve "Alice", "Bob", and the others.

 

My desired result would look like:

Alice | A | 1234
Alice | B | 9876

 

 

Any idea?


Accepted Solutions
Highlighted
Nine Stars

Re: tFileInputJSON access iterating key value

Hi,

 

I don't know if there is an easy way to achieve this but you should be able to produce the desired output by using a mix of Java code.

 

First, load your json file in one global variable (for example, tFileInputFullRow -> tFlowToIterate with empty row delimiter).

 

Once you have the json in memory, you should be able to iterate over the keys with a tJavaFlex, retrieve the needed data with one or more tExtractJSONFields, and finally, build the desired output with a tJavaRow (the tJavaRow is needed to retrieve previous data).

 

Here is a full example :

I first load the following json in a "line" global variable

{"Alice": [{"A": 1234, "B": 9876}, {"A": 4321, "B": 6789}],
"Bob": [{"A": 2468, "B": 9753}, {"A": 8642, "B": 3579}]}

 

Then, I use this part of job :

javaw_20190626_100633_059.png

 

The tJavaFlex is used to iterate over the keys. It has two output columns (id and data both as String).

Import (advanced settings) :

import java.util.Iterator;
import org.json.JSONArray;
import org.json.JSONObject;

Initial code :

String json = ((String)globalMap.get("line")); //retrieve the loaded json
JSONObject j_json = new JSONObject(json); //build a JSONObject from it

Iterator<String> keys = j_json.keys(); //get the keys and loop

while(keys.hasNext()) {

Main code :

  String key = keys.next(); // retrieve the key
// setup output values
row2.id = key; // the id column will be used in the tJavaRow row2.data = j_json.getJSONArray(key).toString(); // the data column will be used in the tExtractJSONFields

End code :

}

 

The tExtractJSONFields retrieve the A and B data (it has 2 output columns A anb B both as String) :

JSON Fields : data
Loop Jsonpath query : "$.[*]"
Mapping : A="@.A" and B="@.B"

 

The tJavaRow build the final dataset (it has 3 columns: id, A and B all Strings) :

output_row.id = row2.id; // note how the row2 is used here even if it's not the input row
output_row.A = input_row.A; // A and B comes from the previous component
output_row.B = input_row.B;

 

Of course, it's possible to use the same method with multiple tExtractJSONFields for nested arrays.

The tJavaRow can rebuild the whole output by accessing data from previous rows (using the correct row id as above for the row2).

 

Regards.

View solution in original post


All Replies
Highlighted
Five Stars

Re: tFileInputJSON access iterating key value

I found a way around but I believe it's not the best way to achieve my requirement:

tFile -> tFileInputDelimited -> tMap -> ... 

 

Nope

 

Highlighted
Nine Stars

Re: tFileInputJSON access iterating key value

Hi,

 

I don't know if there is an easy way to achieve this but you should be able to produce the desired output by using a mix of Java code.

 

First, load your json file in one global variable (for example, tFileInputFullRow -> tFlowToIterate with empty row delimiter).

 

Once you have the json in memory, you should be able to iterate over the keys with a tJavaFlex, retrieve the needed data with one or more tExtractJSONFields, and finally, build the desired output with a tJavaRow (the tJavaRow is needed to retrieve previous data).

 

Here is a full example :

I first load the following json in a "line" global variable

{"Alice": [{"A": 1234, "B": 9876}, {"A": 4321, "B": 6789}],
"Bob": [{"A": 2468, "B": 9753}, {"A": 8642, "B": 3579}]}

 

Then, I use this part of job :

javaw_20190626_100633_059.png

 

The tJavaFlex is used to iterate over the keys. It has two output columns (id and data both as String).

Import (advanced settings) :

import java.util.Iterator;
import org.json.JSONArray;
import org.json.JSONObject;

Initial code :

String json = ((String)globalMap.get("line")); //retrieve the loaded json
JSONObject j_json = new JSONObject(json); //build a JSONObject from it

Iterator<String> keys = j_json.keys(); //get the keys and loop

while(keys.hasNext()) {

Main code :

  String key = keys.next(); // retrieve the key
// setup output values
row2.id = key; // the id column will be used in the tJavaRow row2.data = j_json.getJSONArray(key).toString(); // the data column will be used in the tExtractJSONFields

End code :

}

 

The tExtractJSONFields retrieve the A and B data (it has 2 output columns A anb B both as String) :

JSON Fields : data
Loop Jsonpath query : "$.[*]"
Mapping : A="@.A" and B="@.B"

 

The tJavaRow build the final dataset (it has 3 columns: id, A and B all Strings) :

output_row.id = row2.id; // note how the row2 is used here even if it's not the input row
output_row.A = input_row.A; // A and B comes from the previous component
output_row.B = input_row.B;

 

Of course, it's possible to use the same method with multiple tExtractJSONFields for nested arrays.

The tJavaRow can rebuild the whole output by accessing data from previous rows (using the correct row id as above for the row2).

 

Regards.

View solution in original post

Highlighted
Five Stars

Re: tFileInputJSON access iterating key value

@lennelei Thank you so much, that is precise and exact!

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

Talend Studio Improvements for API Services

Take a look at the Talend Studio improvements for API Services

Watch Now