Loading Json (Json inside array of json) response to a Lakehouse Delta tables was not successful

[bala_ci]

Hi All,

My goal: Loading Json response to multiple tables

Steps in the pipeline to the above:

1) Copy acitivity - which will connect REST API and JSON response saved as json file in the Lakehouse file section - Successful

2) On completion of above activity (1), Copy activity which reads the json file from the lakehouse and thru Import schema - Mapping done for "Sales" (Json parent records) and copied to a delta table "sales" - Successful

3) On completiono of above activity (1), Copy activity which reads the json file from the lakehouse file and thru Import schema Mapping done for a inside Json array. For example, ['sales'][0]['orders'] - Project reference to import the schemas and store it to different tables "orders". - Successfull but only records in the array[0] is copied to the tables not all the records in the Json.

4) On completion of above activity (1), Copy activity which reads the json file from the lakehouse file and thru Import schema Mapping done for a inside Json array 'discounts'. - Not successful. Not all sales have discounts json array. Even though I struggled to map the discounts json format but records are not stored into tables. First json records does not have discounts so it stopped there.

My queries:

1) How to debug the pipelines activity?

2) Why only one array values are copied to the tables not all the records in the json?

3) Why if the json contains a empty json array are not skipped instead entire copy process is stopped? 

Please help me to solve the above issues. If you need any clarification, please let me know.

Thanks

Bala.

[GeethaT-MSFT]

Hi @bala_ci Thanks for posting your question in Microsoft Fabric Community

Currently, I am checking with the Internal team on your ask and provide you with more details once I hear back from the team.

Regards

Geetha

[bala_ci]

Any Updates

[ajarora]

For question#2: you may need to specify that `sales` is a collection, have you tried using the setting: "collection as reference" ?

More details here: https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping#hierarchi...

For question#3: I am not sure about your question, are you expecting the copy activity to have not stopped when the array was empty, and keep proceeding to copy the rest of the data ? What do you mean it stopped, did the activity fail ?

Can you try to enable "Skip incompatible rows" option under Settings -> Fault Tolerance and see if it helps ?