Multiple Collection Reference to copy API JSON data to SQL

Question

Multiple Collection Reference to copy API JSON data to SQL

Kunal Kumar Sinha 171

Hi, I'm trying to fetch data from API whose response is nested json, I used collection reference to capture all the results but the results have arrays in which by default only first value is captured. I checked the documentation also (https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping ) and there it has mentioned only single array is supported for such operation.
Please let me know how i can capture all the nested array json values.
I tried using the Map complex values to string option also but it's not working.

PFA for the json result and ADF mapping done.

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-09T11:43:45.217+00:00

Hi @Kunal Kumar Sinha ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

Yes, it is a known limitation in the copy activity. A workaround that works in this case is to use flatten transformation in dataflows.

Please suggest if this helps in achieving the requirement, for any challenges, kindly share the JSON file that helps us to repro and share the detailed approach.
Kunal Kumar Sinha 171 Reputation points

2020-11-09T15:17:02.257+00:00

Hi @HarithaMaddi-MSFT ,

Thanks for your reply! Can you please share a similar use case implementation for reference. Please find attached sample json response where I have Trigger, Firm as array which has two objects inside. Please note this thing can be present for any of the nodes of json response and are not static. 38389-response.txt
Vikas Gupta 1 Reputation point

2021-07-28T13:51:19.627+00:00
I am trying the same with small JSON with multiple array..its not working for me. The 5th column (PlaceID) is storing as NULL when doing copy activity.
with data flow it is not showing the same option as mentioned in the gif.
could you guys help me if anyone able to load data into SQL with multiple JSON array

{
"deviceid":"1E01363532383334",
"event":[
{
"name":"Wall",
"count":1
}
],
"context":{
"custom":{
"dimensions":[
{
"Wall":"Dot"
},
{
"PlaceID":"ca6b-4bf1-93c4-113627b56e28"
}

] }

},
"EventProcessedUtcTime":"2021-07-15T02:51:28.0826576Z"
}

1 answer

Your answer

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-09T11:43:45.217+00:00

Hi @Kunal Kumar Sinha ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

Yes, it is a known limitation in the copy activity. A workaround that works in this case is to use flatten transformation in dataflows.

Please suggest if this helps in achieving the requirement, for any challenges, kindly share the JSON file that helps us to repro and share the detailed approach.
Kunal Kumar Sinha 171 Reputation points

2020-11-09T15:17:02.257+00:00

Hi @HarithaMaddi-MSFT ,

Thanks for your reply! Can you please share a similar use case implementation for reference. Please find attached sample json response where I have Trigger, Firm as array which has two objects inside. Please note this thing can be present for any of the nodes of json response and are not static. 38389-response.txt
Vikas Gupta 1 Reputation point

2021-07-28T13:51:19.627+00:00

I am trying the same with small JSON with multiple array..its not working for me. The 5th column (PlaceID) is storing as NULL when doing copy activity.
with data flow it is not showing the same option as mentioned in the gif.
could you guys help me if anyone able to load data into SQL with multiple JSON array

{
"deviceid":"1E01363532383334",
"event":[
{
"name":"Wall",
"count":1
}
],
"context":{
"custom":{
"dimensions":[
{
"Wall":"Dot"
},
{
"PlaceID":"ca6b-4bf1-93c4-113627b56e28"
}

] }

},
"EventProcessedUtcTime":"2021-07-15T02:51:28.0826576Z"
}

Answer 1

HarithaMaddi-MSFT 10,136

Thanks @Kunal Kumar Sinha for sharing the file. Below is an approach I have used to get the data for triggers and firms arrays in the given file. Attached the dataflow JSON to the issue.

38818-dataflowjson.txt

However, when you mentioned static, please suggest if it means that triggers/firms may not occur under results node always as in sample file. As per my understanding, if root(Results in this case) needs to be identified dynamically, we need to alter the ARM templates and use deployment approach to achieve it.

Please suggest for more queries and we will be glad to assist further.

--------------------------

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
Want a reminder to come back and check responses? Here is how to subscribe to a notification.

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-12T06:52:14.467+00:00

Hi @Kunal Kumar Sinha ,

We have not received a response from you. Please let us know if above solution addressed the requirement, let us know for any further queries and we will be glad to assist.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members
Kunal Kumar Sinha 171 Reputation points

2020-11-12T12:23:40.35+00:00

Hi @HarithaMaddi-MSFT ,

My API is https://www.XX.com/api/e/v1/insights?tags_id=XX&start_date=XX&page=X
I have few questions regarding the solution provided by you. It will be very helpful if you can answer it.
Can I make a API call using Data Flow?
Can I implement the manual pagination in Data Flow to iterate through the number of pages the API sends as response which is identified by the next node in json response, if the current page is last next value will be null(empty).
I need to pass 16 tags_id and start date from 1st Jan 2020
PFA sample json response for one of the tag id for particular day
If you check the result node 9, it has 2 triggers, 2 firms values, it can have more number of values also and same can happen for any other node as well.
Answering to your question, all the nodes will come as a response but it is possible that it has no values.
The json response will be in this format only.39328-response.txt
HarithaMaddi-MSFT 10,136 Reputation points

2020-11-12T13:11:49.9+00:00

Thanks @Kunal Kumar Sinha for sharing queries.

One approach I can think of is foreach activity will be used for iterating all the pages inside which copy activity can copy the response into a blob file and dataflow can be triggered later to access and process this file.

If the number of values under trigger/firm varies, that's acceptable for this approach suggested above.

Please suggest if this works, let us know for more queries and we will be glad to assist.
HarithaMaddi-MSFT 10,136 Reputation points

2020-11-16T07:01:21.107+00:00

Hi @Kunal Kumar Sinha ,

We have not received a response from you. Please suggest if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community member
HarithaMaddi-MSFT 10,136 Reputation points

2020-11-17T08:40:22.893+00:00

Hi @Kunal Kumar Sinha ,

We still have not heard from you. Following up to check if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community member
Kunal Kumar Sinha 171 Reputation points

2020-11-19T11:36:34.813+00:00

Hi @HarithaMaddi-MSFT ,

Sorry for my delayed response, Can you please breakdown the solution steps. It will be very helpful to me. Can the flattening be done without storing the JSON response in blob storage ? Another doubt I had is if my API response has results in multiple pages say 10, so do I have to store all the json response pages separately or can it be appended to one json response ? And if it can not be appended then how data flow will pick all the pages of API response and flatten it to store it in Azure SQL ?
HarithaMaddi-MSFT 10,136 Reputation points

2020-11-20T14:32:17.2+00:00
Hi @Kunal Kumar Sinha ,

Please find below my thoughts for the queries

Since dataflow has to read the schema of JSON data, it has to be stored in a place where it can be accessed as a source. It is fine even if it is in Azure SQL.

If multiple page results needs to be converted into a single output for dataflow, I can think of using an array variable initially to capture all the JSON outputs of the pages using append activity and then converting array into a string variable that can be loaded as JSON into Blob/SQL

Please let me know for further queries and we will be glad to assist.
HarithaMaddi-MSFT 10,136 Reputation points

2020-11-24T14:16:45.223+00:00

Hi @Kunal Kumar Sinha ,

We have not received a response from you. Please suggest if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community member
HarithaMaddi-MSFT 10,136 Reputation points

2020-11-25T15:01:00.657+00:00

Hi @Kunal Kumar Sinha ,

We still have not heard back from you. Following up to check if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community member
Kunal Kumar Sinha 171 Reputation points

2020-11-25T15:07:50.203+00:00

Hi @HarithaMaddi-MSFT ,

Can you please provide a use case or some example where we can use a variable to store the API json response and keep on appending to it for all the pages a response has.

Thanks,
Kunal
HarithaMaddi-MSFT 10,136 Reputation points

2020-11-26T15:54:24.16+00:00

Hi @Kunal Kumar Sinha ,

Thanks for posting the query. I was suggesting to use Array variable and use append activity to add responses to this array. Later this can be converted to string, reference use case for conversion can be found in these- thread1 and thread2. Please let us know for further queries, we will be glad to assist.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members.
HarithaMaddi-MSFT 10,136 Reputation points

2020-11-27T15:51:15.123+00:00

Hi @Kunal Kumar Sinha ,

We have not received a response from you. Please suggest if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community member
HarithaMaddi-MSFT 10,136 Reputation points

2020-11-30T08:54:38.417+00:00

Hi @Kunal Kumar Sinha ,

We still have not heard back from you. Following up to check if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members
Kunal Kumar Sinha 171 Reputation points

2020-12-01T09:04:07.72+00:00

Hi @HarithaMaddi-MSFT , Thank you for keeping the chain active, I'm facing some issues in using the set and append variable to store the API response. The API response that I'm getting using web activity is in type object. Can you please help me on this ? Also once i have the final result using append variable activity how can i write that to my blob storage ?
Kunal Kumar Sinha 171 Reputation points

2020-12-02T09:01:49.053+00:00

Hi @HarithaMaddi-MSFT ,

The solution suggested by you using dataflow, in that can't i use the flatten activity continuously instead of reading again from source ? Please let me know

Thanks,
Kunal
HarithaMaddi-MSFT 10,136 Reputation points

2020-12-02T10:57:35.98+00:00

Thanks @Kunal Kumar Sinha for coming back to the thread. Since question posted by you refers to multiple page results that needs to be converted into a single output for dataflow, I suggested using set and append activities prior to triggering dataflow. This thread shows implementation of loading variable into a blob, kindly refer to it and let me know for questions on the same. Object references can be accessed by referring to response element and that can be loaded in a string variable, please share sample response or the challenge faced while assigning this to a variable that can enable us to help you better.
Kunal Kumar Sinha 171 Reputation points

2020-12-02T14:37:44.04+00:00
Hi @HarithaMaddi-MSFT ,

Can you please let me know whether the nested array data can be fetched as a complex string value in the pipeline where i can get the entire array as a string ?

I'm not able to understand what will be the source in that case, since I'm running my pipeline in until loop can you please let me know what will be my source ?
HarithaMaddi-MSFT 10,136 Reputation points

2020-12-03T11:43:55.27+00:00

Thanks @Kunal Kumar Sinha for additional queries. It is hard to follow as we are going deep to specific requirement, I would suggest you to send a mail to AzCommunity[at]microsoft[dot]com with subject: "Attn:Haritha - Multiple Collection Reference to copy API JSON data to SQL" and our team can help further on this.

Thanks for your support!

Share via

Multiple Collection Reference to copy API JSON data to SQL

1 answer

Your answer