question

KunalKumarSinha-4793 avatar image
0 Votes"
KunalKumarSinha-4793 asked VikasGupta-1266 commented

Multiple Collection Reference to copy API JSON data to SQL

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://docs.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.


38026-adfcapture.png38027-jsoncapture.png


azure-data-factory
adfcapture.png (21.8 KiB)
jsoncapture.png (64.9 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KunalKumarSinha-4793,

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.

0 Votes 0 ·

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


0 Votes 0 ·
response.txt (8.7 KiB)

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"
}

0 Votes 0 ·

1 Answer

HarithaMaddi-MSFT avatar image
0 Votes"
HarithaMaddi-MSFT answered HarithaMaddi-MSFT commented

Thanks @KunalKumarSinha-4793 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.

38793-multiplearraysjsonadf.gif
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.



· 18
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KunalKumarSinha-4793,

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

0 Votes 0 ·

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


0 Votes 0 ·
response.txt (64.7 KiB)

Thanks @KunalKumarSinha-4793 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.

0 Votes 0 ·
Show more comments