Json file load in adf

Question

Hi, how to run json file on synapse workspace.. Tried with data flow but not reading files after data using flattens transformation... convert in parquet..Pls share screenshot(file attached). have selected data.ap_lt_os_pri.idfs_calculated_options.edit_all in unroll by but not getting full data...Screenshot is appreciated

Answer 1

Step 1: Load JSON File in Synapse Data Flow

1. Create a Data Flow:
   • Go to the Synapse Studio, under the Develop tab, create a new Data Flow.
   • Add a Source transformation and configure it to point to your JSON file in the Data Lake or Blob Storage.
   • Set the file format to JSON.
2. Inspect and Parse JSON Structure:
   • Use the Data Preview tab to inspect the JSON structure.
   • Ensure that the JSON is correctly read and all the nested fields are visible.

Step 2: Flatten the JSON Data

1. Add Flatten Transformation:
   • Drag the Flatten transformation from the toolbox into the data flow.
   • Connect the Source to the Flatten transformation.
   • In the Flatten transformation, under Unroll By, select the complex structure you want to flatten.
   • Since your JSON has deeply nested structures, ensure you properly unroll all levels to get the needed fields.
   Example:
   • If your JSON has a structure like data.ap_lt_os_pri.idfs_calculated_options, select data in the Unroll By option.
   • You might need to add multiple Flatten transformations if the JSON structure is deeply nested.

Step 3: Convert to Parquet

1. Sink Transformation:
   • Add a Sink transformation.
   • Connect the output of the Flatten transformation to the Sink.
   • Configure the Sink transformation to save the data in Parquet format.
   • Set the output folder and file name in the desired storage account.

Useful Links

• Flatten Transformation in Azure Data Factory
• Mapping Data Flows in Azure Data Factory
• JSON Format in Azure Data Factory