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

@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.

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.