JSON Nested Array data as String value

Question

JSON Nested Array data as String value

Kunal Kumar Sinha 171

Hi,

I'm trying to fetch data from API and store it in Azure Sql, the api response has nested array data (PFA sample file) I need to capture all the nested records under result node. I tried using map complex values to string option in mapping section of copy data activity but it's not returning the entire array as string value so that i can use sql script to parse it. Please provide some solution to this.

Thanks,
Kunal

45199-responsefile-783-15174-page-1.txt

MartinJaffer-MSFT 26,236 Reputation points

2020-12-07T21:00:38.333+00:00

Hello @Kunal Kumar Sinha and thank you for your question.

To better assist you, could you please clarify which connector / activity you are using to fetch the data? This has an effect on what solutions are possible. Please include linked service type and dataset type.

For example, if you are using HTTP connector, we can leverage the legacy dataset model to capture the entire response without parsing by skipping the format settings.
Kunal Kumar Sinha 171 Reputation points

2020-12-08T04:57:05.597+00:00

Hello @MartinJaffer-MSFT ,

I'm using REST type dataset to connect to API and fetch the data to store it in Azure Sql, In this way I was able to capture only the first record of the nested array values present in the API response as collection reference can be applied to only one array which I'm applying to result array to capture all the results. But result array has sub set of arrays which has multiple values which I'm not able to capture except the first values of each sub set of arrays. That's why I was looking for some solution to capture the entire nested arrays as string values which can be parsed using some scripts.
If you can suggest some other way to capture the nested values in which I don't have to use scripts to parse it at later stage that will be very helpful. Please find attached sample API response file. 45986-responsefile-783-15174-page-1.txt

Thanks,
Kunal

Accepted answer

1 additional answer

Your answer

MartinJaffer-MSFT 26,236 Reputation points

2020-12-07T21:00:38.333+00:00

Hello @Kunal Kumar Sinha and thank you for your question.

To better assist you, could you please clarify which connector / activity you are using to fetch the data? This has an effect on what solutions are possible. Please include linked service type and dataset type.

For example, if you are using HTTP connector, we can leverage the legacy dataset model to capture the entire response without parsing by skipping the format settings.
Kunal Kumar Sinha 171 Reputation points

2020-12-08T04:57:05.597+00:00

Hello @MartinJaffer-MSFT ,

I'm using REST type dataset to connect to API and fetch the data to store it in Azure Sql, In this way I was able to capture only the first record of the nested array values present in the API response as collection reference can be applied to only one array which I'm applying to result array to capture all the results. But result array has sub set of arrays which has multiple values which I'm not able to capture except the first values of each sub set of arrays. That's why I was looking for some solution to capture the entire nested arrays as string values which can be parsed using some scripts.
If you can suggest some other way to capture the nested values in which I don't have to use scripts to parse it at later stage that will be very helpful. Please find attached sample API response file. 45986-responsefile-783-15174-page-1.txt

Thanks,
Kunal

Answer 1

Kunal Kumar Sinha 171

Hi @HimanshuSinha-msft ,

I used Advanced editor section in mapping field to capture the entire array field using wildcard '*'.

Thanks,
Kunal

Answer 2

MartinJaffer-MSFT 26,236

Thank you for the sample data @Kunal Kumar Sinha .

The preferred way to handle this type of deeply nested JSON is a 2-step process.

First, capture the $.results , copying from the REST api to either blob storage or ADLS gen2.

Second, load the stored data using Mapping Data Flow. In the Data Flow we can use the Flatten/Unroll transformation to break the data out into a tabular format as desired before pushing to Azure Sql.

The first step is necessary because (last I checked) Mapping Data Flow cannot load directly from REST.

Before I go deeper, do you have any objection to using Mapping Data Flows?

HimanshuSinha-msft 19,486 Reputation points Microsoft Employee Moderator

2020-12-17T21:53:38.35+00:00

Hello ,
We have not heard back from you on this and was just following up .
Incase if you have resolution , request you to share the same here , so that other community members can benefit from that .
Thanks
Himanshu
Lex Page 1 Reputation point

2021-12-13T13:02:54.837+00:00

Hi @MartinJaffer-MSFT , I have been looking for some guidance / documentation around the solution you have suggested above but cannot seem to find anything suitable or detailed enough.
Do you happen to have any resources that you could suggest please? My employer is looking to pull a Flood Alerts API feed (Nested JSON) into our Data Lake Environment for further analysis and to task responders. Any help would be greatly appreciated.

Share via

JSON Nested Array data as String value

1 additional answer

Your answer