Parse bigJson (nested Json) files in Azure Data Lake Gen 2 and saving data into separate normalized tables in Azure SQL

Question

We have BigJson files (complex nested Json files 10 levels deep) stored in Azure Data Lake gen 2. We need to read the Json files, parse them and store the data into separate tables in Azure SQL DB.
So, it's NOT like parsing the Json into one single flattened denormalized table in SQL DW.
Can we do this by Azure Data Factory? What is the best way to achieve this?

Answer 1

Hello @Anonymous and welcome to Microsoft Q&A.

If I understand you right, you want to not only flatten a very deeply nested JSON, but you want to split it into multiple tables. This is beyond the ability of Copy Activity, but I think Data Flow can accomplish this. This task sounds non-trivial, especially if the nesting forks.

In the tags, you included Data Factory, Synapse, and Databricks. Can I take this to mean you are comfortable using a code-based solution , not just a visual interface? If that is so, more options become available, such as writing custom code to do the work in Synapse and Databricks notebooks / jars. There is also custom code in the Custom Activity.

If all else fails there is using the SQL JSON functions.

Whether do go Data Flow or custom code depends upon volume and complexity.

As I don't know the details of your JSON structure, nor the criteria or desired output, I can't provide more precise advice.