Consume REST API with nested output

[Andruasi]

Hey folks - 

New here, looking for some help !!

Working on calling REST API which has JSON output. The output I see is in nested format and I am looking for some guidance on how to append select statement to read all the data. 

API connection worked fine but it gives data preview as single array of record as below,

Here's sample screenshot of output,

Here's the default system generated SQL statement,

Any guidance would be really appreciated !!

Thanks in advance.

[QFabian]

Hi @Andruasi ,  maybe you can check the WITH CONNECTION clause, that can be added to your load from the API.  i used it within a for next cycle, changing parameters to get all the records. 

[Andruasi]

Thanks @QFabian for your suggestion. Issue I am facing is not with query parameters or getting not all records. I am able to get the output but output is showing as one single array of record. I am looking forward to split all the fields rather just like below,

Was wondering if SQL Select needs to be enhanced to handle nested JSON output but not sure how.

Appreciate all the help !