topic Re: Create complex json with tWriteJSONField in Talend Studio

Create complex json with tWriteJSONField

MCANTAL1639145723 — Sat, 21 Jan 2023 21:43:33 GMT

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.

Re: Create complex json with tWriteJSONField

Anonymous — Mon, 23 Jan 2023 11:18:47 GMT

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.

Re: Create complex json with tWriteJSONField

MCANTAL1639145723 — Mon, 23 Jan 2023 13:57:34 GMT

I am using the free edition.

Can you explain me how to use several tWriteJSONField components ?

Re: Create complex json with tWriteJSONField

Anonymous — Mon, 23 Jan 2023 14:56:54 GMT

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.

Re: Create complex json with tWriteJSONField

MCANTAL1639145723 — Mon, 23 Jan 2023 21:22:31 GMT

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 ?

Re: Create complex json with tWriteJSONField

Anonymous — Tue, 24 Jan 2023 11:58:44 GMT

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.

Re: Create complex json with tWriteJSONField

MCANTAL1639145723 — Tue, 24 Jan 2023 17:04:55 GMT

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

Re: Create complex json with tWriteJSONField

Anonymous — Tue, 24 Jan 2023 17:46:05 GMT

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....

Re: Create complex json with tWriteJSONField

MCANTAL1639145723 — Fri, 27 Jan 2023 12:13:13 GMT

Thanks, it's working.

How not to write fields with null values ?

Thanks.