topic Extracting rows from a single http string response in Talend Studio

Extracting rows from a single http string response

Dobby1 — Wed, 10 Apr 2019 18:06:26 GMT

I'm trying to get data via an API call to a web application using tHttpRequest (or tRESTClient). The calls to the API works but it returns all the rows of data in a variable "string". I've tried tExtractJSONFileds and tExtractDelimitedFields but not getting the result I expect which is several rows of data. An excerpt of the string variable is shown below:

{"COLUMNS":["STUDENTNUMBER","APPLICATIONTYPENAME","APP_RECEIVED","APP_COMPLETE","TIMEFRAMENUMERICCODE","ELECTRONIC_SIG_TS","CONTRACT_RECEIVED","APP_CANCELED","DEPOSIT","DEPOSIT_AMOUNT","DEPOSIT_RECEIVED","PAYVENDORCONFIRMATION","UNDERAGE","UNDERAGE_ELECTRONIC_SIG_TS","INSURANCE_INTENT"],"DATA":[["R099999","Incoming FIRST YEAR Student Housing Application","February, 27 2017 16:29:38",-1,201709,null,null,null,0,0.0000,null,null,0,null,"No Response"],["R088888","Incoming FIRST YEAR Student Housing Application","February, 19 2017 12:37:24",-1,201709,null,null,null,0,0.0000,null,null,0,null,"No Response"],["R077777","Incoming FIRST YEAR Student Housing Application","February, 17 2017 18:45:56",-1,201709,null,null,null,0,0.0000,null,null,0,null,"No Response"]]}

The "COLUMNS": tag tells the list of columns that are contained in the string and the "DATA": tag marks the beginning of the actual data. Each row of data is demarcated with open and closed angle brackets and separated by a comma. The column separator is a comma and the fields are enclosed within double quotes.

How would I process this string field to extract each row of data?

Re: Extracting rows from a single http string response

Dobby1 — Wed, 10 Apr 2019 21:15:05 GMT

We have found the issue relating to why the tExtractJSONFileds did not process the returned string as we expected. The API is served via ColdFusion which does not return the data in JSON format by default.

The default form for query serialization looks a bit like so:

{"COLUMNS":["NAME","AGE"],"DATA":[["ray",33],["todd",43],["scott",53]]}

To have it return it in JSON format we had to include &returnformat=json&queryformat=struct as parameters in the call to the API.

Thanks to this article below:

https://www.raymondcamden.com/2014/05/08/ColdFusion-11s-new-Struct-format-for-JSON-and-how-to-use-it-in-ColdFusion-10.

The issue is solved and can be closed. Thank you.

Re: Extracting rows from a single http string response

Anonymous — Mon, 15 Apr 2019 07:56:01 GMT

Hello,

Thanks for your feedback and sharing your solution with us.

Best regards

Sabrina