ADF - converting list of lists into a proper JSON format

Patryk Sobczak 0

Hello,

I'm pretty new to ADF and I can't wrap my head around one case.

To keep it simple:

I have a source (REST API response), which theoretically is JSON but it doesn't have a "proper" JSON format.

It's supposed to be a simple table but it looks like that:

{
  "Fields": [
    "Region",
    "Market",
    "Country/Territory",
    "City",
    "2031"
  ],
  "Data": [
    [
      "Europe",
      "Central Europe",
      "Czech Republic",
      "Kvasiny",
      43323
    ],
    [
      "Europe",
      "Central Europe",
      "Czech Republic",
      "Mlada Boleslav",
      40224
    ],
    [
      "Europe",
      "Central Europe",
      "Hungary",
      "Esztergom",
      40416
    ],
    [
      "Europe",
      "Central Europe",
      "Hungary",
      "Kecskemet",
      0
    ]
  ]
}

Now, I'd like to use a Parquet file or at least a JSON file as a sink.

How can I approach this?

I was playing with mapping data flows but I'm not sure on how to proceed.

phemanth 10,325 Microsoft Vendor

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

phemanth 10,325 Reputation points Microsoft Vendor

2024-09-30T09:42:28.84+00:00

@Patryk Sobczak We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Patryk Sobczak 0 Reputation points

2024-09-30T11:36:32.24+00:00

@phemanth Thanks a lot for your input. It looks promising and helpful but I did not have a chance to test it yet (a change in priorities appeared in the meantime).
Hopefully will be able to do that this week.
Patryk Sobczak 0 Reputation points

2024-09-30T11:38:53.4466667+00:00

I will keep you posted once I get there :-)

Patryk Sobczak 0

Hello @phemanth

I was trying to accomplish what you have suggested and I got stuck on the Derived Column step. I have no idea what I'm missing.

Here's how it looks like:

User's image

There are warnings about casting byName values. After using toString function nothing actually changes in regards to results.

Then, the data preview looks as follows:
User's image

One additional remark, the logic for derived column is being put into the script, instead of, as in your example, JSON typeProperties:

{
    "name": "SPGLOBAL_savedsearch_LVPropMarket",
    "properties": {
        "type": "MappingDataFlow",
        "typeProperties": {
            "sources": [
                {
                    "dataset": {
                        "referenceName": "SPGLOBAL_savedsearch",
                        "type": "DatasetReference"
                    },
                    "name": "SPGLOBALsavedsearchLVPropMarket",
                    "description": "Import data from SPGLOBAL_savedsearch_LVPropMarket"
                }
            ],
            "sinks": [
                {
                    "dataset": {
                        "referenceName": "Parquet_SPGLOBAL_savedsearch",
                        "type": "DatasetReference"
                    },
                    "name": "sink1"
                }
            ],
            "transformations": [
                {
                    "name": "derivedColumn1"
                }
            ],
            "scriptLines": [
                "source(output(",
                "          body as (Data as string[][], Fields as string[]),",
                "          headers as [string,string]",
                "     ),",
                "     allowSchemaDrift: true,",
                "     validateSchema: false,",
                "     httpMethod: 'GET',",
                "     timeout: 30,",
                "     requestInterval: 0,",
                "     responseFormat: ['type' -> 'json', 'documentForm' -> 'documentPerLine']) ~> SPGLOBALsavedsearchLVPropMarket",
                "SPGLOBALsavedsearchLVPropMarket derive(Region = byName('body.Data'),",
                "          Market = byName('body.Data')) ~> derivedColumn1",
                "derivedColumn1 sink(allowSchemaDrift: true,",
                "     validateSchema: false,",
                "     format: 'parquet',",
                "     umask: 0022,",
                "     preCommands: [],",
                "     postCommands: [],",
                "     skipDuplicateMapInputs: true,",
                "     skipDuplicateMapOutputs: true) ~> sink1"
            ]
        }
    }
}

Patryk Sobczak 0 Reputation points

2024-10-02T12:27:02.5633333+00:00

Do you have any suggestions on what I might be doing wrong?

phemanth 10,325 Microsoft Vendor

@Patryk Sobczak

Casting byName Values:

The warnings about casting byName values suggest that the data types might not be matching. Using toString is a good approach, but let’s ensure it’s applied correctly.

Ensure that the syntax for the Derived Column transformation is correct. Here’s an example of how you can define the derived columns:

{
  "name": "derivedColumn1",
  "type": "DerivedColumn",
  "typeProperties": {
    "columns": {
      "Region": "toString(byName('body.Data'))",
      "Market": "toString(byName('body.Data'))",
      "Country/Territory": "toString(byName('body.Data'))",
      "City": "toString(byName('body.Data'))",
      "2031": "toString(byName('body.Data'))"
    }
  }
}

If the data preview is not showing the expected results, double-check the mappings and ensure that the fields are correctly referenced.
It seems like the logic for the derived column is being put into the script. Ensure that the script correctly references the fields and transformations. Here’s an example script:

{
  "name": "SPGLOBAL_savedsearch_LVPropMarket",
  "properties": {
    "type": "MappingDataFlow",
    "typeProperties": {
      "sources": [
        {
          "dataset": {
            "referenceName": "SPGLOBAL_savedsearch",
            "type": "DatasetReference"
          },
          "name": "SPGLOBALsavedsearchLVPropMarket",
          "description": "Import data from SPGLOBAL_savedsearch_LVPropMarket"
        }
      ],
      "sinks": [
        {
          "dataset": {
            "referenceName": "Parquet_SPGLOBAL_savedsearch",
            "type": "DatasetReference"
          },
          "name": "sink1"
        }
      ],
      "transformations": [
        {
          "name": "derivedColumn1",
          "type": "DerivedColumn",
          "typeProperties": {
            "columns": {
              "Region": "toString(byName('body.Data'))",
              "Market": "toString(byName('body.Data'))",
              "Country/Territory": "toString(byName('body.Data'))",
              "City": "toString(byName('body.Data'))",
              "2031": "toString(byName('body.Data'))"
            }
          }
        },
        {
          "name": "Flatten1",
          "type": "Flatten",
          "typeProperties": {
            "unrollBy": "body.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"
              }
            ]
          }
        }
      ]
    }
  }
}

phemanth 10,325 Reputation points Microsoft Vendor

2024-10-04T03:07:17.8066667+00:00

@Patryk Sobczak We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

Sina Salam 10,491 Reputation points

2024-09-25T19:58:58.4666667+00:00
Hello Patryk Sobczak,

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

I understand that you would like to manage the conversion and storage of your data using Azure Data Factory.

Regarding your questions, using a Parquet or JSON File as a Sink required many steps however, the links below will guide you through:

https://learn.microsoft.com/en-us/azure/data-factory/format-parquet

https://learn.microsoft.com/en-us/azure/data-factory/create-datasets-portal#sink-datasets

https://learn.microsoft.com/en-us/azure/data-factory/connector-json

https://learn.microsoft.com/en-us/azure/data-factory/connector-parquet

Also, use this link below to see more details on how to convert List of Lists into Proper JSON Format in ADF:

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-overview

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-transform-flatten

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-transform-aggregate

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-expression-functions#json-functions

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.
Please sign in to rate this answer.
Patryk Sobczak 0 Reputation points

2024-09-26T09:21:37.4233333+00:00

Hello @Sina Salam

Thanks for that but it's just not detailed enough.

Correct, I'd like to perform transformation in ADF and I already did several similar flows with different other APIs.

Therefore, all the steps around (linked service, source, sink, etc.) are already covered and working great - I've skipped that in my post to shorten the whole story.

I also know all the elements documentation to which you've provided links to (by the way, most of links are not working) and I have already tried several approaches to no avail (flatten was actually my first choice but it doesn't seem to work since I'm dealing with lists (or arrays) rather than with JSON objects.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

ADF - converting list of lists into a proper JSON format

1 answer

Your answer