CODEWITHSUNDEEP
azure-data-factory
Travis
Travis

Reputation: 261

Whats the Easiest Way to Extract All Columns From a Complex Type Using ADF Mapping Data Flow

I have a json file I am transforming with an ADF Data Flow. The file has has some complex type properties which have a 1 to 1 relationship with the main row, it is not an array. I know I can extract columns from the complex type using a derived column such as MyDerivedColumn1 = ComplexType.Column1, MyDerivedColumn2 = ComplexType.Column2, etc..; however there are a lot of columns in the complex type and the creation of a derived column for each is a bit tedious. I was hoping someone could enlighten me to some magical button or function which could do all that work for me similar to when transforming a complex type in Power BI. In PBI all I have to do is click a button at the column header of the complex type and it adds a Table.ExpandRecordColumn function to the M code and all the complex type columns are promoted to the top level record.

A simplified version of the json would look like this...

{
    "Col1": "Col1Value",
    "Col2": "Col2Value",
    "Col3": "Col3Value",
    "ComplexType": {
        "ComplexCol1": "ComplexType1Value",
        "ComplexCol2": "ComplexType2Value",
        "ComplexCol3": "ComplexType3Value"
    }
}

Upvotes: 0

Views: 1258

Answers (1)

Rakesh Govindula
Rakesh Govindula

Reputation: 11454

You can use select transformation to extract these dynamically.

This is sample data from the above JSON:

enter image description here

In select transformation, Go to Add mapping -> Rule-based mapping and give it like below. Delete the original mapping for the ComplexType object so that you won't get that JSON in your output.

enter image description here

Result:

enter image description here

Upvotes: 1

Related Questions