Create complex json with tWriteJSONField

[MCANTAL1639145723]

Hi,

I have to create a json object whose format is attached. I can map.

I will use tmap to map the data and then I have to use a twritejsonfield component. How should i configure this component ?

Thanks for your help.

[Anonymous]

This is going to require a little more than a single tWriteJSONField component. Are you using Talend Open Studio (the free edition) or the Enterprise edition? If you are using the Enterprise edition, you would probably be better off doing this with the Data Mapper. If you are using Talend Open Studio, you will either need to use several tWriteJSONField components (with a bit of String manipulation) or build this with a bit of Java. To be honest, I would go with building this with a bit of Java if I were using Talend Open Studio for this.

[MCANTAL1639145723]

I am using the free edition.

Can you explain me how to use several tWriteJSONField components ?

[Anonymous]

OK, here is a very basic example. I am using what I have seen from your JSON to demonstrate an inner loop. You can only really effectively handle one loop at a time per component. So I am writing the "countries" loop.

This is a simple job. The tFixedFlowInput has some of the data you shared. The tWriteJSONField component will build the "countries" loop. The tLogRow will display it.

As you can see, I have included only a small section of the data....

This is how I have mapped the data to the JSON structure that I have built. Notice the 2 countries elements. The first one will give the element name in the output. The second one just drives the loop.

Notice the grouping here. I am grouping the output JSON by the languageCode. It will produce a different section of JSON per languageCode. Since I am supply two languageCodes (en and fr), you will see two JSON Strings produced.

 

This is the JSON that is produced....

{"languages":{"languageCode":"en","colour":"rouge","details":{"product":{"countries":[{"step":"Step 1","countryCode":"CHN"},{"step":"Step 2","countryCode":"FRA"},{"step":"Step 3","countryCode":"DEU"}]}}}}

{"languages":{"languageCode":"fr","colour":"rouge","details":{"product":{"countries":[{"step":"Step 1","countryCode":"CHN"},{"step":"Step 2","countryCode":"FRA"},{"step":"Step 3","countryCode":"DEU"}]}}}}

Once you have this, you will need to consider this another "column" of data when you are building the outer loop in the same way.

As I said, this can be fiddly. I generally prefer to spend a bit of time using Java to do this. It can take a while to perfect, but once it is done, it is easily reusable and very logical to read.

[MCANTAL1639145723]

Thanks it will help me a lot.

You write "you will need to consider this another "column" of data when you are building the outer loop in the same way." but how to link this new column with the data of the outer loop ?

[Anonymous]

This is not obvious, but it is quite simple. Pass the key data column through with the other data. Instead of using it in your JSON, create an extra output column (along with your JSON output column) and add it to the Group By. Then you JSON will be passed through along with our key column.

[MCANTAL1639145723]

I'm not sure I understand evrything, can you give me an example?

[Anonymous · Accepted solution]

I have extended the example I gave above to show this.

Here is the tFixedFlowInput....where my data comes from. See I have added a "key" field. I have added the values "bob" and "bob3".....just random values that came to me.

Now see that I have added this column to the grouping of the tWriteJSONField component. I also added the "key" column to be an output column from this component.

When I now run this, I get the following....

{"languages":{"languageCode":"en","colour":"rouge","details":{"product":{"countries":[{"step":"Step 1","countryCode":"CHN"},{"step":"Step 2","countryCode":"FRA"},{"step":"Step 3","countryCode":"DEU"}]}}}}|Bob

{"languages":{"languageCode":"fr","colour":"rouge","details":{"product":{"countries":[{"step":"Step 1","countryCode":"CHN"},{"step":"Step 2","countryCode":"FRA"},{"step":"Step 3","countryCode":"DEU"}]}}}}|Bob3

[MCANTAL1639145723]

Thanks, it's working.

How not to write fields with null values ?

 

Thanks.