ADF - converting list of lists into a proper JSON format

@Patryk Sobczak

Thank you for posting query in Microsoft Q&A Platform.

Let’s dive deeper into the specifics of transforming your list of lists into a proper JSON format using Azure Data Factory (ADF).

Detailed Steps

• Ensure your source dataset is correctly configured to read the JSON data from your REST API.
• Create a new Data Flow in ADF.
• Add a Source transformation and point it to your source dataset.
• Add a Derived Column transformation to create a new column for each field in your “Fields” array. This will help in mapping the data correctly.
• Example:JSON

    {
      "Region": byName("Data"),
      "Market": byName("Data"),
      "Country/Territory": byName("Data"),
      "City": byName("Data"),
      "2031": byName("Data")
    }
    
  

• Add a Flatten transformation to flatten the “Data” array.
• In the Flatten transformation, set the “Unroll by” to the “Data” array.
• Add a Select transformation to map the flattened fields to the desired schema.
• Rename the columns as needed to match your target schema.
• Add a Sink transformation and configure it to write to a Parquet or JSON file.
• Ensure the sink dataset is correctly configured to point to your desired storage location.

Example JSON Data Flow

Here’s an example of how your JSON data flow might look:

JSON

{
  "name": "FlattenJSONDataFlow",
  "properties": {
    "type": "MappingDataFlow",
    "typeProperties": {
      "sources": [
        {
          "name": "Source1",
          "dataset": {
            "referenceName": "YourSourceDataset",
            "type": "DatasetReference"
          }
        }
      ],
      "transformations": [
        {
          "name": "DerivedColumn1",
          "type": "DerivedColumn",
          "typeProperties": {
            "columns": {
              "Region": "byName('Data')",
              "Market": "byName('Data')",
              "Country/Territory": "byName('Data')",
              "City": "byName('Data')",
              "2031": "byName('Data')"
            }
          }
        },
        {
          "name": "Flatten1",
          "type": "Flatten",
          "typeProperties": {
            "unrollBy": "Data"
          }
        },
        {
          "name": "Select1",
          "type": "Select",
          "typeProperties": {
            "select": [
              {
                "name": "Region",
                "alias": "Region"
              },
              {
                "name": "Market",
                "alias": "Market"
              },
              {
                "name": "Country/Territory",
                "alias": "Country"
              },
              {
                "name": "City",
                "alias": "City"
              },
              {
                "name": "2031",
                "alias": "Year2031"
              }
            ]
          }
        },
        {
          "name": "Sink1",
          "type": "Sink",
          "dataset": {
            "referenceName": "YourSinkDataset",
            "type": "DatasetReference"
          }
        }
      ]
    }
  }
}

Troubleshooting Tips

• Check Data Types: Ensure that the data types of your columns are correctly set in the Derived Column transformation.
• Debugging: Use the Data Flow Debug feature to preview the data at each transformation step.
• Error Messages: Pay attention to any error messages or warnings in the ADF UI, as they can provide clues on what might be going wrong

Hope this helps. Do let us know if you any further queries.