Can't parse JSON correctly

One Star

Can't parse JSON correctly

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

One Star

Re: Can't parse JSON correctly

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?
Ten Stars

Re: Can't parse JSON correctly

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".
One Star

Re: Can't parse JSON correctly

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?
One Star

Re: Can't parse JSON correctly

May it be connected with this issue:

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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog