Search code examples
jsonazurecsvazure-data-factoryflatten

API with nested JSON parse into CSV file in ADF

I get a generated Json file from the Log Analytics API into ADF, and I want to convert it to CSV file. My Json file has this structure:

{
    "tables": [
        {
            "name": "PrimaryResult",
            "columns": [
                {
                    "name": "PipelineName",
                    "type": "string"
                },
                {
                    "name": "Status",
                    "type": "string"
                },
                {
                    "name": "TimeGenerated",
                    "type": "datetime"
                },
                {
                    "name": "_ResourceId",
                    "type": "string"
                },
                {
                    "name": "Type",
                    "type": "string"
                }
            ],
            "rows": [
                [
                    "Get_Tables",
                    "Succeeded",
                    "2023-09-04T08:15:48.77315Z",
                    "/subscriptions/144612ca-37be-4643-9c80-5706a350bdb2/resourcegroups"
                ],
                [
                    "LogAnaytics",
                    "Succeeded",
                    "2023-09-04T08:15:55.073672Z",
                    "/subscriptions/providers/microsoft.datafactory/factories",
                    "ADFPipelineRun"
                ]
            ]
        }
    ]
}

I tried to flaten, firstly table than columns than rows like this:enter image description here but it did not work. I want to get a result in csv like this:

enter image description here

Solution

  • To achieve your requirement, follow below steps, I took your sample Json and transformed it:

    • First import the projection properly in source. enter image description here data preview: enter image description here

    For Columns

    • First flatten the columns using flatten activity. enter image description here data preview: enter image description here
    • Add a Surrogate column enter image description here data preview: enter image description here
    • Then using pivot transformation convert columns into row. enter image description here No need to set group by enter image description here data preview: enter image description here

    For Rows

    • Then flatten the rows. enter image description here data preview: enter image description here
    • Using derived column transformation unfold nested array. enter image description here data preview: enter image description here
    • Then use select transformation and delete array column. enter image description here data preview: enter image description here

    Now using Union transformation combine the rows and columns

    enter image description here

    data preview: enter image description here

    Now, using sink transformation load data into CSV

    • Sink settings Add CSV dataset in Sink and follow below settings. enter image description here

    Output: enter image description here