Struggling to extract API response to table format

[welshsteve]

Hi.

I have been trying to split out a response from tRESTClient into table format using tExtractJSONFields, but am not getting anything in the results.

Here is the config of my

tExtractJSONFields component.

This is what the tRESTClient response looks like in its raw format.

And this is the result of running the job. As you can see I have NULL it both columns.

What I am expecting to see is a table like below:

Apologies if I've made no sense but I am not that knowledgeable in this area and struggling to understand what I do to get the data I want extracted.

[Anonymous]

What you are trying to do is not possible here. The tExtractJSONFields returns a row of data for every iteration of the loop specified. These loops are JSON arrays. Since you are not pointing to an array in the loop parameter, you will get one row back. The paths you have specified to get the type and subtype are pointing to an array that doesn't exist. They are two arrays within an object. What you can do is extract the full of contents of each array and then produce the table you require afterwards. You would extract all of the "Sales" array in one go and all of the "Customer Service" array in one go. Then break that data down knowing that everything in the "Sales" array is type "Sales" and everything in the "Customer Service" array is type "Customer Service".

Another way of doing this is to use two tExtractJSONFields components and set the loop array to be "$.choice_category_dependence.Sales[*]" in one and "$.choice_category_dependence.['Customer Service'][*]" in the other. You could have only one output (for the array values) and that path would be "$".

Notice the the difference between the loop paths....

"$.choice_category_dependence.Sales[*]"

"$.choice_category_dependence.['Customer Service'][*]"

The second style is used because there is a space in the element name. So you add square brackets and single quotes so that JSONPath queries can be used.