Home
%3CLINGO-SUB%20id%3D%22lingo-sub-880516%22%20slang%3D%22en-US%22%3EADF%20Adds%20Hierarchical%20%26amp%3B%20JSON%20Data%20Transformations%20to%20Mapping%20Data%20Flows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880516%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20Azure%20Data%20Factory%20team%20has%20released%20JSON%20and%20hierarchical%20data%20transformations%20to%20Mapping%20Data%20Flows.%20With%20this%20new%20feature%2C%20you%20can%20now%20ingest%2C%20transform%2C%20generate%20schemas%2C%20build%20hierarchies%2C%20and%20sink%20complex%20data%20types%20using%20JSON%20in%20data%20flows.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133930iED4EA1D9D09A8AF7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22json5.png%22%20title%3D%22json5.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20the%20sample%20data%20flow%20above%2C%20I%20take%20the%20Movies%20text%20file%20in%20CSV%20format%2C%20generate%20a%20new%20complex%20type%20called%20%22Movies%22%20that%20contains%20each%20of%20the%20attributes%20of%20the%20incoming%20CSV%20file.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133931i48B3BDCEC5C0359F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22json6.png%22%20title%3D%22json6.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EBy%20using%20the%20Expression%20Builder%20(see%20above)%20inside%20of%20a%20Derived%20Column%20transform%2C%20I%20can%20define%20a%20new%20hierarchical%20structure%20that%20includes%20arrays.%20Genres%20is%20a%20multi-value%20field%20from%20the%20source%20CSV%20that%20contains%201%3An%20genre%20categories%20for%20movies%20via%20a%20pipe%20(%7C)%20delimited%20field.%20I%20can%20use%20the%20ADF%20data%20flow%20expression%20language%20to%20split%20all%20values%20from%20that%20string%20and%20store%20each%20value%20as%20an%20array%20element.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133932i0E7CFDD2C66266EE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22json4.png%22%20title%3D%22json4.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EMapping%20Data%20Flows%20now%20has%20the%20ability%20to%20show%20metadata%20for%20complex%20types%20including%20hierarchies%20and%20arrays%20in%20both%20metadata%20Inspect%20views%20as%20well%20as%20in%20Data%20Preview.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133933i32B19F7E45908A6C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22json2.png%22%20title%3D%22json2.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133934iE196FAF6D749EBB9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22json1.png%22%20title%3D%22json1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENow%20that%20I've%20transformed%20my%20flat%20file%20into%20a%20hierarchical%20JSON%20format%2C%20I%20can%20sink%20this%20as%20a%20new%20JSON%20file%20to%20my%20Blob%20or%20lake%20using%20a%20JSON%20Sink.%20To%20create%20this%20new%20file%2C%20I'll%20map%20just%20the%20new%20complex%20types%20by%20selecting%20the%20top-level%20%22Movies%22%20hierarchy.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133935i817B8F168DC1D99C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22json7.png%22%20title%3D%22json7.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EJSON%20datasets%20can%20also%20now%20become%20source%20files%20for%20Data%20Flows.%20In%20this%20case%2C%20I'll%20read%20in%20my%20new%20JSON%20dataset%20from%20the%20above%20example%20and%20perform%20some%20transformations.%20I%20want%20to%20upper%20case%20each%20of%20the%20genres.%20To%20do%20this%2C%20I%20can%20apply%20a%20single%2C%20simple%20expression%20in%20a%20Derived%20Column%2C%20that%20will%20upper%20case%20each%20element%20in%20my%20Genres%20array%20that%20I%20created%20previously.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133936iDC859ECB997C6947%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22json3.png%22%20title%3D%22json3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWe've%20posted%20a%20helper%20document%20on%20our%20ADF%20documentation%20site%20to%20help%20you%20work%20through%20more%20examples%20of%20working%20with%20hierarchies%2C%20arrays%2C%20and%20JSON%20datasets%20in%20ADF%20Mapping%20Data%20Flows%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fconcepts-data-flow-json%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-880516%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20Azure%20Data%20Factory%20team%20has%20released%20JSON%20and%20hierarchical%20data%20transformations%20to%20Mapping%20Data%20Flows%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-880516%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Data%20Factory%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EJson%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMapping%20Data%20Flows%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-882428%22%20slang%3D%22en-US%22%3ERe%3A%20ADF%20Adds%20Hierarchical%20%26amp%3B%20JSON%20Data%20Transformations%20to%20Mapping%20Data%20Flows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-882428%22%20slang%3D%22en-US%22%3E%3CP%3ESuper%20handy%20feature%20to%20avoid%20doing%20extra%20steps%20to%20deal%20with%20this%20kind%20of%20situations!!%20Thanks%20for%20adding%20this%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

The Azure Data Factory team has released JSON and hierarchical data transformations to Mapping Data Flows. With this new feature, you can now ingest, transform, generate schemas, build hierarchies, and sink complex data types using JSON in data flows.

json5.png

In the sample data flow above, I take the Movies text file in CSV format, generate a new complex type called "Movies" that contains each of the attributes of the incoming CSV file.

json6.png

By using the Expression Builder (see above) inside of a Derived Column transform, I can define a new hierarchical structure that includes arrays. Genres is a multi-value field from the source CSV that contains 1:n genre categories for movies via a pipe (|) delimited field. I can use the ADF data flow expression language to split all values from that string and store each value as an array element.

json4.png

Mapping Data Flows now has the ability to show metadata for complex types including hierarchies and arrays in both metadata Inspect views as well as in Data Preview.

json2.pngjson1.png

Now that I've transformed my flat file into a hierarchical JSON format, I can sink this as a new JSON file to my Blob or lake using a JSON Sink. To create this new file, I'll map just the new complex types by selecting the top-level "Movies" hierarchy.

json7.png

JSON datasets can also now become source files for Data Flows. In this case, I'll read in my new JSON dataset from the above example and perform some transformations. I want to upper case each of the genres. To do this, I can apply a single, simple expression in a Derived Column, that will upper case each element in my Genres array that I created previously.

json3.png

We've posted a helper document on our ADF documentation site to help you work through more examples of working with hierarchies, arrays, and JSON datasets in ADF Mapping Data Flows here.

1 Comment
Occasional Visitor

Super handy feature to avoid doing extra steps to deal with this kind of situations!! Thanks for adding this one.