Extracting Data from a JSON with pagination to an excel

Anonymous — Wed, 17 Apr 2019 11:48:39 GMT

I have extracted data from an API with pagination. I have attached screenshots for all my components and I'm getting an output for every page in the API. I need to extract these information into an excel but the output will not be coming as a tabular format (see the output figure).

The issue why my data comes in one row is my data is under "$.task_assignments.[*]" and the next page url is in "$.links.[*]" path of JSON (see the attached JSON image).

If I define the Loop Jsonpath query as "$.task_assignments.[*]" and extract data I can get my data in a tabular format but I can't get the URL and If I define the Loop Jsonpath query as "$.links.[*]" then I can get the URL and in that case I don't get the data.

So as a solution I set up the Loop Jsonpath query as "$" which is a common path but in that case although the data comes it doesn't comes in a tabular format.

I hope you can understand what I'm asking. So it would be great if someone can help in extracting these data into one table and export into excel

Re: Extracting Data from a JSON with pagination to an excel

Anonymous — Wed, 17 Apr 2019 18:37:22 GMT

Why not build urls custom? No need to read the json each time to get the next url.

You can do an initial call to the api and extract total_pages, put that in a tLoop from 1 to total_pages,

then just append ((Integer)globalMap.get("tLoop_1_CURRENT_VALUE")) to your tRestClient query string

so it looks something like this:

"https://api.harvestapp.com/v2/task_assignments_page="+ ((Integer)globalMap.get("tLoop_1_CURRENT_VALUE")) +"&per_page=100"

This way your jsonpath from tExtractJSONFields wont have to change, and you'll get only the relevant data you need.

topic Extracting Data from a JSON with pagination to an excel in Talend Studio

Extracting Data from a JSON with pagination to an excel

Re: Extracting Data from a JSON with pagination to an excel