How to Flatten nested JSON file in ADF without using dataflow.

Question

this is your input json file

ID	Name	Design	Date	Salary
ID	Name	Design	Date	Salary
1	Shreyasi	[Consultant, S.Con1, S.Con2]	[01-02-20,04-05-23,07-07-24]	[100, 300,305]
2	Basava	[S.Con1,S.con2, Manager]	[01-06-22, 01-07-23, 01-0125]	[100, 200, 500]
3	Mohan	[Analyst,Consultant]	[01-04-22,11-08-23]	[100, 200]

 

 

this is output you have to get it using data factory. We cannot use data flows

ID	Name	Design	Date	Salary
ID	Name	Design	Date	Salary
1	Shreyasi	Consultant	01-02-2020	100
1	Shreyasi	S.Con1	04-05-2023	300
1	Shreyasi	S.Con2	07-07-2024	305
2	Basava	S.con1	01-06-2022	100
2	Basava	S.Con2	01-07-2023	200
2	Basava	Manager	13-01-2025	500
3	Mohan	Analyst	01-04-2022	100
3	Mohan	Consultant	11-08-2023	200

 

 

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

Below is one possible pattern you can follow in adf to flatten a nested JSON without using data flows. The broad idea is:

1. Load your raw JSON into a staging location (such as a table in Azure SQL or a file in Data Lake).
2. Use a mechanism (a stored procedure in Azure SQL) to read and unnest the JSON into a final, flattened table or file.
3. Copy out the flattened table/file to your final destination.

highlevel steps:

1. Create a staging table in Azure SQL DB (or Synapse SQL) that can store the JSON contents.
2. Use Copy Activity in ADF to copy the raw JSON as-is into the staging table.
3. Create a stored procedure that:
   • Reads the JSON string(s) from the staging table.
   • Parses and unnests the arrays into multiple rows.
   • Inserts the flattened rows into a final table (or returns them in a result set).
4. Call that stored procedure from a Stored Procedure Activity in ADF.
5. (Optional) Copy from the final flattened table out to Blob Storage or any other sink.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.

Answer 2

@Praveen Kabadagi

Welcome to the Microsoft Q&A and thank you for posting your questions here

To flatten a nested JSON file in Azure Data Factory (ADF) without using data flows, you can use the Copy Activity with a custom mapping. Here are the steps to achieve this:

Create a Pipeline:

• In the Azure portal, navigate to your Data Factory service.
• Click on "Pipelines" and then "Create pipeline."

Add a Copy Activity:

• Drag and drop a "Copy data" activity from the "Activities" pane onto the designer canvas.

Configure the Source:

• Double-click the "Copy data" activity.
• Click on "Source" and choose the dataset where your nested JSON file resides. This could be Azure Blob Storage, ADLS Gen2, or another supported source.
• Select the appropriate format (e.g., JSON) and configure the file path or connection details.

Configure the Sink:

• Click on "Sink" and choose the dataset where you want to save the flattened data. This could be a CSV file in Azure Blob Storage, ADLS Gen2, or another supported destination.

Mapping:

• Click on "Mapping" to define the custom mapping.
• Map the nested JSON fields to the flat structure. You will need to create multiple mappings for each nested array element. For example:
• Map Design[0] to Design
• Map Date[0] to Date
• Map Salary[0] to Salary
• Repeat for Design[1], Date[1], Salary[1], etc.

Run the Pipeline:

• Save and run the pipeline to flatten the JSON data and write it to the specified sink.

Hope this helps. Do let us know if you any further queries.________If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.