Talend - how to create a collection of JSON objects

Question

I need to create collections of JSON objects from REST requests (see post Talend - URI based on values obtained from a list). A webservice provides a collection of agencies in the following format:

{ "COD_AGENCIA":"521800300", "NAME":"AGENCIA01"},
{ "COD_AGENCIA":"521999999", "NAME":"AGENCIA02"},
...
(20 other values)
...

Another webservice, from an agency code, provides the cities related to this agency, in the following format:

{"COD_MUN":"5202155","NOME_MUN":"CITY01"},
{"COD_MUN":"5202502","NOME_MUN":"CITY02"},
...

I created a job that gets the agency relationship, and through a tFlowToIterate, I call multiple requests through a tRest, varying the agency code. My job is like this:

When I turn on the output of the last tExtractJSONField in a tLog, the codes and names of all the cities at once they are printed. If I change the tLog for a tFileOutputJSON, the final file contains only the last iteration called cities.

What I need to change to be able to generate in a single job multiple JSON objects in the following format:

{"COD_AGENCIA":"521800300",
 "NAME":"AGENCIA01",
 "CIDADES":[{"COD_MUN":"5202155","NOME_MUN":"CITY01"},
            {"COD_MUN":"5202502","NOME_MUN":"CITY02"}
  ]
}

{"COD_AGENCIA":"521999999",
 "NAME":"AGENCIA02",
 "CIDADES":[{"COD_MUN":"5244887","NOME_MUN":"CITY03"},
            {"COD_MUN":"5254522","NOME_MUN":"CITY04"}
  ]
}

Answer 1

I had the same trouble in a previous project.

In Talend, you CAN'T add a row at the end of a JSON but you can in CSV. I used a CSV to have all rows and send them in a JSON then.

In your case you have multiple levels in your JSON so it will be more complicate ! Talend flow has only one level so generate multiple levels JSON is already complicate.

I suggest to take a look at tJsonDoc* components. I didn't try myself since my "CSV-workaround" fits my needs, but you can create complex structure with this tool, so I guess you can add at the end of the file.

Answer 2

I created one file for each json inside a directory then, using tFileList I iterated over each file, readed and sent it.