topic Re: Can't parse JSON correctly in Talend Studio

Can't parse JSON correctly

_AnonymousUser — Wed, 14 Oct 2015 13:11:38 GMT

I'm trying to automate some APIs. So I started from Google analytics one (I know that Talend has a component for it, I just want to educate myself so that I can then jump to Facebook etc)

Here is my Job:

Response from GA looks like this:

{"kind":"analytics#gaData",
 "id":"",
 "query":{"start-date":"2015-01-01","end-date":"2015-01-10","ids":"ga:1111111","dimensions":"ga:date,ga:medium,ga:browser","metrics":["ga:sessions","ga:bounces"],"start-index":1,"max-results":1000},"itemsPerPage":1000,"totalResults":156,"selfLink":"","profileInfo":{"profileId":"11111111","accountId":"111111","webPropertyId":"UA-1111111","internalWebPropertyId":"11111111","profileName":"aaaaaaaa","tableId":"ga:11111111"},"containsSampledData":false,"columnHeaders":[{"name":"ga:date","columnType":"DIMENSION","dataType":"STRING"},{"name":"ga:medium","columnType":"DIMENSION","dataType":"STRING"},{"name":"ga:browser","columnType":"DIMENSION","dataType":"STRING"},{"name":"ga:sessions","columnType":"METRIC","dataType":"INTEGER"},{"name":"ga:bounces","columnType":"METRIC","dataType":"INTEGER"}],"totalsForAllResults":{"ga:sessions":"907","ga:bounces":"480"},

"rows":[["20150101","(none)","Chrome","3","2"],["20150101","(none)","Safari","2","1"],["20150101","organic","Chrome","9","4"]]}

I specifically want to extract 'rows' part in columns.
I can successfully make it for the first object in the array:

But when I replace "$.rows[0][1]" to "$.rows[][1]" to iterate, I get nulls.
Any help will be appreciated.

Also. How can I save the result of my REST request into the file? tFileJSONoutput adds some weird body object and I can't read anything from the file after that. Can I save plain text into txt?

Thanks

Re: Can't parse JSON correctly

_AnonymousUser — Wed, 14 Oct 2015 21:31:24 GMT

Anyone who knows JSON components well? I would really appreciate your help.

I'm using "jsonquerytool[dot]com" to validate my queries. And by using

$.rows[Asterisk][0]

I'm able to extract all the first elements in rows array.

But how do I make it in Talend?

Re: Can't parse JSON correctly

Anonymous — Wed, 14 Oct 2015 21:45:23 GMT

I've written a tutorial on working with Facebook using Talend (to get Spotify data in this case) and I cover an example of dealing with JSON. You can download the example jobs to try this out. It is reasonably complicated, but if you work through it I think you should be able to extrapolate from it to find your solution. The tutorial is here. The section on getting data from a JSON String is titled "Extract data from JSON".

Re: Can't parse JSON correctly

_AnonymousUser — Wed, 14 Oct 2015 22:25:22 GMT

rhall_2.0 Thanks a lot! Definetly checking it out.

If anyone can help with my situaltion - That would be still good. How do I deal with array of arrays in Talend?

Re: Can't parse JSON correctly

_AnonymousUser — Wed, 14 Oct 2015 23:10:25 GMT

May it be connected with this issue:

[dot]org/forum/viewtopic.php?id=38398