Pentaho DI - JSON Nested File Output

In Pentaho DI, JSON Output Step doesn't support nested dataset. In order to achieve a nested JSON structure, you need to use Javascript step to build the nested structure and eventually pass onto the output.

The usual flow follows: Input -> Group By -> Modified Javascript (JSON.Stringfy) -> Text file output (stored as .js extension without header)

A sample screenshot of the flow is as:

Note: This is not an exact solution but gives a clearer idea of the steps and flow.

Modified JS Step to build the nested structure. Note this is based on a default working database from MySQL (sakila). I am using actor -> film(movie) relation; similar dataset as your student -> subject.

//Initialization Steps -> Change this according to your data setvar json = {};json.movie={};Child_Accounts = []; var split_film_id   =[];var split_title     =[];var split_descr     =[];//Non Nested Structure -> In your scenario these would be Student ID and Namesjson.movie.actor_id     = actor_id;json.movie.first_name   = first_name;json.movie.last_name    = last_name;//splitting the datasplit_film_id   = film_id.split(';');split_title     = title.split(';');split_descr     = description.split(';');// Loop through the splitted data and build the child structurefor(i=0; i<split_film_id.length; i++){    var childCol = {};    childCol.film_id=split_film_id[i];    childCol.title=split_title[i];    childCol.description=split_descr[i];    Child_Accounts.push(childCol); } json.movie.films=Child_Accounts; // JSON Stringify the data object var JsonOutput = JSON.stringify(json);

Rest of the steps are straightforward.

Sample Output

Hope this helps :)

Gist is uploaded here.