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%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%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%3CLINGO-SUB%20id%3D%22lingo-sub-1028637%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-1028637%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20you%20help%20us%20transform%20the%20following%20sample%20JSON%20to%20csv%20using%20ADF%20and%2For%20Data%20Flows.%26nbsp%3B%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%22latest%22%3A%20%222019-10-10T11%3A09%3A09%2B00%3A00%22%2C%3CBR%20%2F%3E%22employees%22%3A%20%7B%3CBR%20%2F%3E%225487%22%3A%20%7B%3CBR%20%2F%3E%22id%22%3A%20%225487%22%2C%3CBR%20%2F%3E%22action%22%3A%20%22Updated%22%2C%3CBR%20%2F%3E%22lastChanged%22%3A%20%222019-10-10T11%3A08%3A53%2B00%3A00%22%3CBR%20%2F%3E%7D%2C%3CBR%20%2F%3E%225491%22%3A%20%7B%3CBR%20%2F%3E%22id%22%3A%20%225491%22%2C%3CBR%20%2F%3E%22action%22%3A%20%22Updated%22%2C%3CBR%20%2F%3E%22lastChanged%22%3A%20%222019-10-10T11%3A09%3A01%2B00%3A00%22%3CBR%20%2F%3E%7D%2C%3CBR%20%2F%3E%225525%22%3A%20%7B%3CBR%20%2F%3E%22id%22%3A%20%225525%22%2C%3CBR%20%2F%3E%22action%22%3A%20%22Updated%22%2C%3CBR%20%2F%3E%22lastChanged%22%3A%20%222019-10-10T11%3A09%3A09%2B00%3A00%22%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%7D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1029755%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-1029755%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20the%20Copy%20Activity%2C%20you%20can%20map%20these%20structures%20to%20targeted%20column%20names%20in%20your%20CSV%20flat%20file%20in%20the%20Sink%20mapping.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Data%20Flows%2C%20you%20will%20create%20a%20Derived%20Column%20and%20build%20a%20flat%20structure%20of%20columns%20that%20map%20to%20the%20different%20structures.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EData%20Flows%20are%20enhancing%20the%20H2R%20capabilities%20in%20the%20tool%2C%20but%20for%20now%2C%20this%20is%20the%20mechanism%20to%20use.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1029984%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-1029984%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Mark%2C%20I%20have%20nested%20JSON%20File%20which%20contains%20data%20in%20array%20format%20also.%20While%20mapping%20I%20am%20getting%20error%20JSON%20Path%20inavalid%20at%20%24%5BResourceManagementProcess%5C%5C'sRunId%5D%26nbsp%3B%20due%20to%20escape%20character.%20I%20am%20using%20copy%20activity.%20Is%20there%20any%20other%20way%20to%20convert%20JSON%20to%20CSV%20format%20as%20data%20flow%20is%20not%20option%20to%20choose.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%7B%0A%20%20%20%20%22id%22%3A%20%22ed0e4960-d9c5-11e6-85dc-d7996816aad3%22%2C%0A%20%20%20%20%22context%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%22device%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22type%22%3A%20%22PC%22%0A%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%22custom%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22dimensions%22%3A%20%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22TargetResourceType%22%3A%20%22Microsoft.Compute%2FvirtualMachines%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22ResourceManagementProcess'sRunId%22%3A%20%22827f8aaa-ab72-437c-ba48-d8917a7336a3%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22OccurrenceTime%22%3A%20%221%2F13%2F2017%2011%3A24%3A37%20AM%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%5D%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%0A%7D%3C%2FPRE%3E%3CP%3E%26nbsp%3B%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.

4 Comments
Visitor

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

Occasional Visitor

Can you help us transform the following sample JSON to csv using ADF and/or Data Flows. 
{
"latest": "2019-10-10T11:09:09+00:00",
"employees": {
"5487": {
"id": "5487",
"action": "Updated",
"lastChanged": "2019-10-10T11:08:53+00:00"
},
"5491": {
"id": "5491",
"action": "Updated",
"lastChanged": "2019-10-10T11:09:01+00:00"
},
"5525": {
"id": "5525",
"action": "Updated",
"lastChanged": "2019-10-10T11:09:09+00:00"
}
}
}

Microsoft

In the Copy Activity, you can map these structures to targeted column names in your CSV flat file in the Sink mapping.

 

In Data Flows, you will create a Derived Column and build a flat structure of columns that map to the different structures.

 

Data Flows are enhancing the H2R capabilities in the tool, but for now, this is the mechanism to use.

Occasional Visitor

Hi Mark, I have nested JSON File which contains data in array format also. While mapping I am getting error JSON Path inavalid at $[ResourceManagementProcess\\'sRunId]  due to escape character. I am using copy activity. Is there any other way to convert JSON to CSV format as data flow is not option to choose.

 

{
    "id": "ed0e4960-d9c5-11e6-85dc-d7996816aad3",
    "context": {
        "device": {
            "type": "PC"
        },
        "custom": {
            "dimensions": [
                {
                    "TargetResourceType": "Microsoft.Compute/virtualMachines"
                },
                {
                    "ResourceManagementProcess'sRunId": "827f8aaa-ab72-437c-ba48-d8917a7336a3"
                },
                {
                    "OccurrenceTime": "1/13/2017 11:24:37 AM"
                }
            ]
        }
    }
}