I'm struggling with anything more than a very simple JSON structure. I can create a JSON that has one array element in it, but as soon as it needs anything more (e.g. an array of questions, each one of which contains an array of answers) I get lost. Are there any good examples of building complex JSON structures with tWriteJSONField components?
Ok here's my quick notes after some experimentation.
1. For each array in a JSON object, you need a tWriteJSONField component.
2. To build the array:
2a. Add elements for the aggregation key, and set the aggregation key in the Component properties
2b. Add a named element to hold the array, give it a class attribute of array
2c. Add an element whose name is not important, give it a class attribute of object
2d. Set the unnamed object element to be the loop element
3. Follow up immediately with a tExtractJSONField component with a Loop Jsonpath query of "$"
4. Map the aggregation key element(s) and the array element into output fields
Now you have the key fields, and the array in a string. You can then join this flow to other flows for other sets of data, and further build up your JSON in more tWriteJSONField components.
The one peculiar thing that baffles me is, the array that you get out from the tExtractJSONField is a string. Normally, when you map a string into a JSON element, it wraps it in quotes. So if the Name column contains Fred, you get this:
However, when you map the constructed array to a JSON element, you get something like this, which is exactly what is wanted:
What I expected to see was this:
Can anyone explain why it is doing what I actually want, rather than what I expected it to do?
Yeah, Talend is supposed to be user-friendly but trust me, do yourself a favor and use an org.json.JSONObject instead. Now that I think about all the time I spent figuring out how to this very simple thing in Talend without real documentation (because you know, they need to work more on that), I could have developed a very sophisticated solution using pure java.
I'd echo navds comments.
Anything more than a simple/flat JSON structure, construct it yourself, making copious use of tHashInput/Output components to store the constructed JSON as an object and tAggregateRow in LIST(Object) method.
Thanks.Could you share a quick example of using org.json.JSONObject with this type of setup? I am not very savvy with using custom java code in tflex/ tjavarow. A quick rundown on how to work through the flows to make the json object will be helpful please.
Maybe someone has a better technique but I don't like to use dozens of tLibraryLoad to import external jars. I prefer adding it in routine dependencies. In this basic example, I downloaded manually the jar json-20171018.jar (yeah, I wish this could be done project-wide using maven), created a new routine 'Demo', edit routine libraries to add the jar. Now I have a JSON parser which could be used project-wise. Here, I returned a java.util.Map which should be familiar.
I hope this helps.
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Watch the recorded webinar!
Pick up some tips and tricks with Context Variables
Learn how media organizations have achieved success with Data Integration
Learn how and why companies are moving to the Cloud