Using ADF Data Flow Derived Column transform against nested Delta structures

%3CLINGO-SUB%20id%3D%22lingo-sub-1808609%22%20slang%3D%22en-US%22%3EUsing%20ADF%20Data%20Flow%20Derived%20Column%20transform%20against%20nested%20Delta%20structures%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1808609%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20use%20a%20derived%20column%20transform%20within%20an%20ADF%20Data%20Flow%20where%20I've%20ingested%20a%20Delta%20table%20with%20nested%20structures.%26nbsp%3B%20I'm%20struggling%20with%20the%20syntax%20needed%20to%20flatten%20out%20these%20structures%20and%20no%20column%20info%20is%20displayed%20despite%20me%20being%20able%20to%20preview%20the%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESuch%20a%20structure%20would%20be%3A%3C%2FP%3E%3CP%3E%7B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%22ContactId%22%3A%221002657%22%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%20%22Name%22%3A%7B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%22FirstName%22%3A%22Donna%22%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%22FullName%22%3A%22Donna%20Brittain%22%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%22LastName%22%3A%22Brittain%22%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%7D%3C%2FP%3E%3CP%3E%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%20Preview%20working%20OK%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ADF_Preview.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F228508i648016BB36BD0281%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22ADF_Preview.jpg%22%20alt%3D%22ADF_Preview.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20structure%20of%20my%20Delta%20table%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DeltaTableFormat.jpg%22%20style%3D%22width%3A%20240px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F228509i5EE28AC799B897AD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22DeltaTableFormat.jpg%22%20alt%3D%22DeltaTableFormat.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20error%20I'm%20getting%20trying%20to%20reference%20a%20nested%20column%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ADF_DerivedCol.jpg%22%20style%3D%22width%3A%20964px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F228510i003EBC5C060811C4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22ADF_DerivedCol.jpg%22%20alt%3D%22ADF_DerivedCol.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20reference%20a%20nested%20column%20such%20as%20Name.FirstName%20to%20flatten%20it%20out%20to%20FirstName%20and%20why%20is%20it%20not%20showing%20up%20in%20any%20of%20the%20mappings%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1808609%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eazure%20data%20factory%20v2%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Regular Visitor

I'm trying to use a derived column transform within an ADF Data Flow where I've ingested a Delta table with nested structures.  I'm struggling with the syntax needed to flatten out these structures and no column info is displayed despite me being able to preview the data.

 

Such a structure would be:

{

  "ContactId":"1002657",

  "Name":{

     "FirstName":"Donna",

     "FullName":"Donna Brittain",

     "LastName":"Brittain"

   }

}

 

Data Preview working OK:

ADF_Preview.jpg

The structure of my Delta table:

DeltaTableFormat.jpg

The error I'm getting trying to reference a nested column:

ADF_DerivedCol.jpg

 

How can I reference a nested column such as Name.FirstName to flatten it out to FirstName and why is it not showing up in any of the mappings?

0 Replies