Can I use tWriteJSON to create a JSON list of N length within an outer list of N length

[aaronrums]

Hello all! My goal is to create a list of "person" objects to pass to an API in JSON format. Most of the data is straightforward - FirstName, LastName, some contact information etc. I've successfully used tWriteJSON to create a list of these person objects pretty well.  All the simple fields are easy.  

However, the person objects has a "roles" attribute, which is a LIST of role OBJECTS. I've attached a sample document of 3 person objects which displays this.  Is it possible for the tWriteJSON component to create a list of persons (which I've done already) but also have a list of any length within each person object? It seems like only one element can be set as the loop.

The data source is a SQL database. 

TLDR:

I have a list of any length of person objects, but within each person objects is a list of role objects.  This role list can also be any length. Can I achieve this with the tWriteJSON component?

[Anonymous · Accepted solution]

You can do this, but you are reaching the limits of what can be easily done within one component here. I have knocked up a quick example to produce a couple of screenshots.

So, first of all you will need to use the group element on your "person" element. This will be your outer array. Then you can use the Loop element on the role object.

When configuring the grouping in the Group by section on the main properties, set the Group by to be a column that is the same for ALL of your data. I have just added a "group" column which is always set to 1.

 

What this does is force the entire data set to appear in one JSON String and allows the person object to be turned into an array outside of the role array.

Not an ideal way of working with arrays, but it works. I tend to end up building my own Java routines to build JSON or using multiple tWriteJSON components sometimes.

[aaronrums]

this was perfect! Thanks so much. Can you explain exactly what the "group by" section does in the basic settings, then also what the node status of "group element" does in the JSON editor? I didn't even know that was an option, it doesn't mention it in the documentation.

Lastly, you mention that this isn't an ideal way of working. Could you briefly explain a better way? You mention using multiple tWriteJSON components, but how would you put them together? I'm somewhat new to Talend, but not data engineering in general.