How to flatten an Array json using Azure Data Flow

Hi @djidji
Welcome to Microsoft Q&A platform and thanks for posting your question here.

To flatten an array JSON and convert it to a Parquet file using Azure Data Flow in Azure Synapse Analytics, you can follow these steps:

• Start by adding your JSON data into the data flow with a source transformation.
• Use the derived column transformation to convert your JSON objects into array items. You can use an expression like split(replace(replace(replace(Info,'[',''),']',''),'},{','}|{'),'|') to achieve this.
• Next, apply the flatten transformation to turn the array values in the “Info” column into multiple rows. You can select the array column under ‘Unroll by’ to flatten the array into multiple rows.
• Then, use the parse transformation to convert the JSON data into columns. You would define the output column type expression as (key as string, value as string) to parse the JSON data into separate columns.
• Finally, in the sink transformation, disable auto-mapping and map the columns as required to write the data into a Parquet file.

Here’s a simplified example of how you might write the transformations in Azure Data Flow:

  {
    "type": "DataFlow",
    "transformations": [
      {
        "name": "Source",
        "type": "Source",
        "dataset": { "referenceName": "JsonDataset", "type": "DatasetReference" }
      },
      {
        "name": "FlattenRows",
        "type": "Flatten",
        "unrollRoot": "rows",
        "unrollPath": "$"
      },
      {
        "name": "DerivedColumn",
        "type": "DerivedColumn",
        "expressions": [
          {
            "column": "Dimensionheaders",
            "expression": "transform(Dimensionheaders, (h) => h.name)"
          },
          {
            "column": "Metricsheaders",
            "expression": "transform(Metricsheaders, (m) => m.name)"
          }
        ]
      },
      {
        "name": "SelectColumns",
        "type": "Select",
        "columns": ["Dimensionheaders", "Metricsheaders", "rows"]
      },
      {
        "name": "SinkToParquet",
        "type": "Sink",
        "dataset": { "referenceName": "ParquetDataset", "type": "DatasetReference" },
        "formatSettings": { "type": "ParquetFormat" }
      }
    ]
  }

Alternatively, you can also query JSON files using serverless SQL pool in Azure Synapse Analytics or convert JSON to Parquet in Azure Synapse Spark Notebook.

Reference:
https://video2.skills-academy.com/en-us/answers/questions/545933/flatten-and-parsing-json-using-azure-data-flow

https://stackoverflow.com/questions/71084684/howto-flatten-simple-json-file-in-azure-synapse-spark-notebook-and-convert-to-pa

I hope this information helps you. Let me know if you have any further questions or concerns.