tFileInputJSON access iterating key value

[Anonymous]

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?

[Anonymous]

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

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

Nope

[lennelei · Accepted solution]

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 :

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.

[Anonymous]

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