Hello, all I am working with XML files that I unzip and then convert to a JSON (because a XML file is sink only). I will then use the JSON file in Data Factory to flatten the data in relevant datasets that I write away to CSV files on my data lake. To standardize this process I created my own standardized JSON file for my hierarchy that I use in the projection.
The issue I am running into is that I cannot drill through the entire hierarchy of the JSON file. So I assumed the problem was my own standardized hierarchy so I let Synapse analytics create its own hierarchy to fix this issue. This resulted in the following two hierarchies: Own hierarchy.PNG and Synpase hierarchy.PNG.
When I previewed the two hierarchies my own hierarchy created all needed columns however all data after Journal was empty. When looking at the automatically created hierarchy it didn’t create any of the columns after journal and created Transaction as a string with all the data after journal in that one column (Preview Synapse hierarchy.PNG).
So when I compared different JSON files some of them did correctly unpack the data. The only difference between these datasets is the first path of the hierarchy. As shown here below the first dataset (JSON File Customer Journal.PNG) shows a JSON file that will not unpack the data further than the journal. The second dataset (JSON File Customer Transaction.PNG) will unpack the transaction just not the trline.
So the problem is the way Azure Synapse Analytics reads the JSON-file. I have no clue how to fix this issue and was wondering if any of you could help me solve this issue.
Thank you for reading my post and hopefully you can help me out.
Lars
