Loosing data from Source to Sink in Copy Data

Question

In my MS Azure datafactory, I have a rest API connection to a nested JSON dataset. The Source "Preview data" shows all data. (7 orders from the online store)

In the "Activity Copy Data", is the menu tab "Mapping" where I map JSON fields with the sink SQL table columns. If I under "Collection Reference" I select None, all 7 orders are copied over. But if I want the nested metadata, I select the meta field in "Collection Reference", then I get my nested data, in multiple order lines, each with a one metadata point, but I only get data from 1 order, not 7

I think I have a reason for my problem. One of the fields in the nested meta data, is both a string and array. But I still don't have a solution

sceen shot of meta data

Answer 1

Your sense is right,it caused by your nested structure meta data. Based on the statements of Collection Reference property:

If you want to iterate and extract data from the objects inside an array field with the same pattern and convert to per row per object, specify the JSON path of that array to do cross-apply. This property is supported only when hierarchical data is source.

same pattern is key point here, I think. However, your data inside metadata array are not same as your screenshot.

My workaround is using Azure Blob Storage to make a transition, REST API ---> Azure Blob Storage--->Your sink dataset. Inside Blob Storage Dataset, you could flatten the incoming JSON data with Cross-apply nested JSON array setting:

You could refer to this blog to learn about this feature. Then you could copy the flatten data into your destination.