Generating and reading JSON data in Talend

Nowadays, there are more and more applications (Salesforce, JIRA Studio) adopting JSON as one of their data-interchange formats. As an integration developer, it is important  to find the right tool that can parse and generate JSON data correctly instead of writing one myself. I guess no one likes to do that. Right?
Luckily I found that there are 2 components in Talend that suit my needs:
Writing JSON – tWriteJSONField (it can generate JSON data according to a specific schema)
Reading JSON – tFileInputJSON (it can read JSON data from local disk or through HTTP call)
Generating JSON data
Here is a simple example of how to read a CSV file and convert the data into JSON format.
CSV data (original data):
WDCi,Lean,KL
WDCi,Kai Herng,KL
WDCi,Walter,Sydney
WDCi,Deborah,Sydney
WDCi,Terry,US
JSON data (result):
{"company":
    {"name":"WDCi",
     "locations":[
          {"location":"KL",
               "staffs":[
                    {"staff":"Lean"},
                    {"staff":"Kai Herng"}
          ]},
          {"location":"Sydney",
               "staffs":[
                    {"staff":"Walter"},
                    {"staff":"Deborah"}
          ]},
          {"location":"US",
               "staffs":
                    {"staff":"Terry"}
          }
     ]
    }
}
Firstly, I will use a tFileInputDelimited component to read the CSV file which has the content shown above from my machine. Below is the schema in my tFileInputDelimited component:

Figure 1.1

Then, I will drop the tWriteJSONField component from the palette to the design workspace and link the output row of the tFileInputDelimited component to it (if you want to manipulate the data before converting it into JSON format, you can use tMap component in between). After that, I need to define the schema for the tWriteJSONField component by clicking on the “Edit schema” button. In this scenario, I will create one column named “output” and set it as the output column of the component. The output row of the tWriteJSONField component is linked to a tLogRow component so that I can log the output in console later.

Figure 1.2

Is that all?! Not yet. Now, I have to configure the JSON tree structure. Below is how my data structure and mapping look like:

Figure 1.3

Do you see that Talend is using XML tree structure here? Yes, this means Talend will prepare the data in XML format before converting into JSON string. It is important to define the “locations” element as “group element” and “staffs” element as “loop element” here because I want to group all staffs by location in the outcome.
Once the structure and mapping are defined, I can run the job to see the result.

Figure 1.4

See that? Now my data is converted into JSON format, but this is not the result that I want because the staff are not grouped according to location. To solve this problem, I just need to add the “company” column as the “Group By” column to the tWriteJSONField component as shown below.

Figure 1.5

 

Oh yes! The result looks better now.

 

Figure 1.6

 

Hopefully, this gives you a basic idea on how to use the tWriteJSONField component in Talend.

 

Get in touch today to see how WDCi can help your business.