JSON file Node didnt loade in SQL server tables using Azure data factory

Question

HI Team,
Below is MY JSON file for SOurce(Blob storage JSON files) I have loading Sql server table using Azure data factory(Copy data activity)
in the Mapping tab they Node checkbox I have selected resolutions Checkbox
data loaded only "optionId": "O1",, "optionId": "O3" rows but I need "optionId": "O2",, "optionId": "O4", also

please anybody help me

{
"report": {
"rows": [
{
"Id": "ID#1",
"openComment": "Open Comment",

            "entities": [
                {
                    "BPId": "BP_ID",
                    "accounts": [
                        {
                            "account_Id": "ZA001",
                            "A_Id": "ENTITY#1_ID",
                            Amount": 1000.0,
                            "Date": "2020-05-12",
                            "Vote": true,
                            "resolutions": [
                                {
                                    "r_Id": "R1",
                                    "r_Descrption_type": "DES",
                                    "r_Descrption_type": "DESC SE",
                                    "options": [
                                        {
                                            "optionId": "O1",
                                            "amount": 20.0
                                        },
                                        {
                                            "optionId": "O2",
                                            "amount": 200.0
                                        }
                                    ]
                                },
                                {
                                    "r_Id": "R2",
                                    "r_Descrption_type": null,
                                    "r_Descrption_type": "ABCVS",
                                    "options": [
                                        {
                                            "optionId": "O3",
                                            "amount": 00.0
                                        },
                                        {
                                            "optionId": "O4",
                                            "amount": 400.0
                                        }
                                    ]
                                }
                            ]
                        }
                    ]
                }
            ]
        }
    ]
}

}

Thanks
Aruna Bai

Answer 1

Hi Aruna,

As of today, only one array can be flattened in a schema. Multiple arrays can be referenced—returned as one row containing all of the elements in the array. However, only one array can have each of its elements returned as individual rows. This is the current limitation with jsonPath.

However you can first convert json file with nested objects into CSV file using Logic App and then you can use the CSV file as input for Azure Data factory. Please refer below URL to understand how Logic App can be used to convert nested objects in json file to CSV.

"https://adatis.co.uk/converting-json-with-nested-arrays-into-csv-in-azure-logic-apps-by-using-array-variable/";

Ref - https://stackoverflow.com/a/58108419/10653466

Hope this helps.