Search code examples
azureazure-data-factorydata-transform

Data Flow transformation on json object where each element is a array

How to convert the following json object:

{"IDpol": [406.0, 407.0, 412.0], "ClaimNb": [1, 1, 1], "Exposure": [0.77, 0.07, 0.84], "Area": ["C", "C", "E"], "VehPower": [9, 9, 7], "VehAge": [0, 0, 3], "DrivAge": [29, 29, 40], "BonusMalus": [72, 72, 54], "VehBrand": ["B12", "B12", "B12"], "VehGas": ["Regular", "Regular", "Regular"], "Density": [360, 360, 5746], "Region": ["R91", "R91", "R11"]}

In the below given csv using azure data factory

IDpol,ClaimNb,Exposure,Area,VehPower,VehAge,DrivAge,BonusMalus,VehBrand,VehGas,Density,Region
406.0 ,   1   ,  0.77  , C  ,   9    ,   0   ,   29  , 72      ,  B12   ,Regular,360   , R91
407.0 ,   1   ,  0.07  , C  ,    9   ,   0   ,   29  , 72      ,  B12   ,Regular,360   ,R91
412.0 ,   1   ,  0.84  , E  ,    7   ,   3   , 40    , 54      , B12    , Regular,5746, R11
Solution

  • I reproduced this and able to get the desired result as follows.

    For this first use dataflow to get all arrays as one array of JSON. Store this result as JSON and then use copy activity to get the csv file of individual columns.

    Use Derived column transformation after source.

    create another temporary column and give the below dynamic expression.

    mapIndex(IDpol, @(Idpol=#item,ClaimNb=ClaimNb[#index],Area=Area[#index],BonusMalus=BonusMalus[#index],DrivAge=DrivAge[#index],Exposure=Exposure[#index],Region=Region[#index],VehAge=VehAge[#index],VehBrand=VehBrand[#index],VehPower=VehPower[#index],VehGas=VehGas[#index],Density=Density[#index]))

    enter image description here

    As length of all arrays are same, the above will give the result as follows.

    enter image description here

    Store this in JSON file. In sink settings give output to single file and specify the file name.

    You can remove the extra columns in sink mapping as we only want temp column.

    enter image description here

    You can see the dataflow output JSON after Executing this dataflow in pipeline.

    enter image description here

    Now, use copy activity with the above JSON as source file and CSV as sink.

    Go to Mapping and click on import mapping. Use Advanced editor and Collection reference to do the correct mapping.

    enter image description here

    You can mention the type also if you want.

    Execute this copy activity and you can get the result CSV like below.

    enter image description here