How to generate and return JSON file from a REST Endpoint

[Anonymous]

I've been working on this for a day or so and haven't made any progress. I'm pretty new to Talend, so i very well could be overlooking something simple. I'm using Talend Studio for Data Services and Big Data 5.6.2
I have the following layout that works great for returning JSON as the response body.
TRESTRequest -> tFlowToIterate -> tJava -> tMysqlInput -> tXMLMap -> tRESTResponse
Now I have a requirement to return the data as a JSON formatted file through the http response . I'm not sure how to go about this. I've tried the tFileOutputJSON component connected to tMysqlInput, but the format is all wrong and there seems to be no option for nested elements. If I put an XMLMap component in between, then it either comes out as a document with all columns as an element of the body node, or as a string with no mapping and it's back to square 1. 
All of the guides I've found only talk about reading a json file or writing a json file from another very, very, very basic finite file. I can't seem to make the two go together. Then after this hurtle, I still don't know how to send this file through the tRESTResponse, but I'm hoping it's as simple as marking the file object a document and setting the response header content-type to  application / zip or whatever.

[Anonymous]

JSON from XML has been a pain to do in Talend for a while. Now it might have changed somewhat recently, but I have used the following method for a while now. Basically it uses the json-org.jar (which can be downloaded here) and a Talend routine. I have included an example of a routine below.....

package routines;
import org.json.JSONException;
import org.json.JSONObject;
import org.json.XML;

public class XmlToJSON {

    public static String xmlToJson(String xml){
    	String json ="";
    	try {
            JSONObject xmlJSONObj = XML.toJSONObject(xml);
            json = xmlJSONObj.toString();
        } catch (JSONException je) {
            System.out.println(je.toString());
        }
		return json;
    }
        
}

Once you have created your routine, right click on the routine in teh project tree and select "Edit Routine Libraries". Then point it towards the Jar downloaded above.
Then all you need to do is create your XML, convert it to a String (use a tConvertType), then use your routine method to convert the XML String to a JSON String.

[Anonymous]

Hi
i need to keep data format in order to have Json file well-formatted.
I need a way to return an object instead of an xml to keep the format.
Ho can i do it? 

[Anonymous]

Hi, if JSON content is already available, then return it as String or byte[], and if this content is zipped/etc then indeed you can customize Content-Type in tRESTResponse/Advanced properties. I think it is important that the studio can be eventually enhanced to support InputStream too.
Cheers, Sergey

[Anonymous]

Hi, if JSON content is already available, then return it as String or byte[], and if this content is zipped/etc then indeed you can customize Content-Type in tRESTResponse/Advanced properties. I think it is important that the studio can be eventually enhanced to support InputStream too.
Cheers, Sergey

Hi
I use a tRouteOutput component instead of a tRESTResponse one.
The problem is that all my JSON fields in the output file are formatted as String, so, for example, the integer fields are surrounded by quotes and i need to have them without.
Unfortunately i cannot manipulate the file after been generated.
Any hint?

[Anonymous]

Bryan,
To return JSON in your response all you need to do is use content negotiation on the client side. Make sure the client is sending a header of Accept: application/json and Talend should do the conversion automatically.
I've written a blog post on this - see http : // integrationwire.com/talend/how-to-return-a-json-response-from-a-talend-job/ (I can't add this as a link because I don't have enough posts yet)

[Anonymous]

Good call monodot. I had completely overlooked that in my answer. My head was firmly in the conversion from XML to JSON 🙂

[Anonymous]

Bryan,
To return JSON in your response all you need to do is use content negotiation on the client side. Make sure the client is sending a header of Accept: application/json and Talend should do the conversion automatically.
I've written a blog post on this - see http : // integrationwire.com/talend/how-to-return-a-json-response-from-a-talend-job/ (I can't add this as a link because I don't have enough posts yet)

Talend does indeed convert to json automatically when the header is present, also, you can set the tRESTRequest to produce only json. That works great for me. My issue is when i want to write the json object to a file, zip the file, and then send the file with an appplication/zip response header.  
I think I will give Rhall's suggestion a try and use the routine to convert the XMLMap to json, then try to write with WriteJSONOutput again. I may have more questions around zipping that file, picking it up, and passing it to the RESTResponse.

[Anonymous]

So, I think I'm getting closer to what I need, but I'm not quite there. Here is my current job set up.
tRESTRequest -> tFlowToIterate -> tJava -> tMysqlInput -> tXMLMap -> tConvertType -> tMap -> tLogRow
I'm just using tLogRow for now because I'm just trying to get the data written to a correctly formatted file before I worry about picking it up and sending it through the http response. tXMLMap is taking the query result and formatting the XML document. tConvert is changing the type to string, tMAP is just being used to call the User Defined routine. and finally tLogRow to check my progress. 
I'm seeing two problems with this at the moment. 
First, the elements are changing data type on me. It will create a JSON array with more than one element, and an object when there is only one element. This, i assume, is because of the "all in one" way I'm creating the JSON string. I believe I can play with this a bit to ensure that loop elements are always arrays. This will be a public API, and I will break contract if it isn't a consistent type. also, it is changing every element to a string, regardless if I have it defined as an Integer. again, I think I can correct this by building out a more robust routine.
Second, it is changing the position of the elements in the structure. For example, my desired JSON format is:
{"products":,"paging":{"page":1, "totalPages":10},"count":100,"serverTime":"2015-06-30T16:26:23000Z"}
but, it is putting all top level elements first, then the arrays or objects.
{"count":100,"serverTime":"2015-06-30T16:26:23000Z","products":,"paging":{"page":1, "totalPages":10}}
I'm not sure of the impact of this yet. Since the top level structure is an object, it may not matter to the client the ordering of the key pairs. 
edit: I also noticed that empty strings get converted to empty json objects, which also will not cut it.

[Anonymous]

So, I was able to get it working.....but this just feels ugly. Attached is the screenshot of my designer.

tRestRequest receives the call from the client and reads any parameters query or header. If there is no request for the data to be returned as a gzip, then it moves through the top job. A-OK. worked like a charm in a few hours.
IF they are requesting the data be zipped, which will be the case in the event of a bulk extract request, the flow moves to the bottom jobs. I used temporary files thinking they would be cleaned up after the call was complete, but realize that they end when the job is killed. So I'll probably change that and add a bit to delete the files when the response is sent.
The bottom most row is where I feel like it's the messiest. I couldn't get the data extracted from the database into the correct format without the tXMLMap component. However, this returns a document object. I needed to convert that to a string to use rhall's suggested XML -> JSON routine. Works great, by the way. Thanks. I used the variable section in the tMAP component to call the routine for no real reason except that it was easier than a tJavaRow. 
The tReplace component was because of the limitations of, pretty much every JSON conversion class I could find. All integers were changes to string, the root element was not able to be removed or ignored, and in the case of a null or empty value, an empty JSONObject was created instead of an empty string.  All of that had to be accounted for to meet the requirements.
I then had to output this as a Raw file and create another routine to create a gzip file from the Raw file. Using OnComponentOK links were important here so the process on the server could complete before moving on. 
The last piece was surprisingly easy, given how long the conversion took. tFileInputRaw picked up the .gz file as a byte array and passed to the tRESTResponse with an added Response Header "Content-Type" "application/gzip"
Putting this out there in case someone else needs to give it a go, but especially incase someone else has a cleaner way of doing this. I do realize that some of the component's tasks could have been achieved with custom code, but not everyone on my team has development experience, and this type of behavior will need to be created by them for other services.