Flatten SOAP api JSON file

Question

Flatten SOAP api JSON file

SW 20

I have a JSON file with sub-arrays that I get as a SOAP api response. I would like to flatten the data by using the ADF Data Flow. However, the "unroll by" or "unroll root" doesn't identify the fields. How can I get this done? JSON file is attached.

1 answer

Your answer

Answer 1

@SW

Welcome to Microsoft Q&A platform and thanks for posting your question.

Flattening nested JSON data in Azure Data Factory (ADF) Data Flow can be achieved using the flatten transformation. Let’s break down the steps:

Flatten Transformation:

The flatten transformation is used to take array values inside hierarchical structures (such as JSON) and unroll them into individual rows. This process is known as denormalization.
- You can find the flatten transformation in the mapping data flow within both Azure Data Factory and Azure Synapse Pipelines.
  - It allows you to unroll arrays and create one row per item in each array.

Configuration:

The flatten transformation has the following configuration settings:
Unroll By: Select an array to unroll. The output data will have one row per item in each array. If the unroll-by array in the input row is null or empty, there will be one output row with unrolled values as null.
You can unroll more than one array per flatten transformation by clicking the plus (+) button.
You can use ADF data flow meta functions (such as name and type) and pattern matching to unroll arrays that match specific criteria.
When including multiple arrays in a single flatten transformation, the results will be a cartesian product of all possible array values.
Unroll Root: By default, the flatten transformation unrolls an array to the top of the hierarchy it exists in. Optionally, you can select an array as your unroll root. The unroll root must be an array of complex objects that either is or contains the unroll-by array.
If an unroll root is selected, the output data will contain at least one row per item in the unroll root. Input rows without any items in the unroll root will be dropped from the output data.
Choosing an unroll root will always output a less than or equal number of rows than the default behavior.
Flatten Mapping: Similar to the select transformation, choose the projection of the new structure from incoming fields and the denormalized array. If a denormalized array is mapped, the output column will be the same data type as the array.
Rule-Based Mapping: The flatten transformation supports rule-based mapping, allowing you to create dynamic and flexible transformations based on rules and hierarchy levels.

Example:
- Suppose you have a complex nested JSON structure with sub-arrays. Here’s how you can flatten it:
- Add two source transformations pointing to the same source JSON.
- Select ‘Array of documents’ in the JSON settings for both sources.
- Add flatten transformations to each of the sources.
- For one flatten transformation, select rows [] to unroll by, and for the other one, select columns [ to unroll by
- .Remember to verify your mapping output using the inspect tab and data preview. With these steps, you’ll be able to flatten your nested JSON data efficiently using ADF Data Flow 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.

SW 20 Reputation points

2024-02-05T11:32:31.52+00:00

Hi phemanth, Thanks for the detailed explanation and steps. I am facing an issue with this. When I add the source, the data preview looks like this:

Hence it is not identifying the array directly as the source. Hence when I add the flatten transformation, the input under inspect is blank and so is the unroll by and unroll root.
So, how can I get just the item array under maintenance (which also has the subarrays) for flatten?
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-02-06T07:28:42.18+00:00

@SWI understand that you're encountering an issue with flattening your JSON data in ADF Data Flow because the source isn't recognizing the array directly. I've analyzed the image you sent, and here are some steps you can try to address this:

1. Use JSON Root Path:

In the "Source" transformation properties, under the "Settings" tab, look for the "JSON root path" setting.

By default, this might be set to $. Try changing it to the specific path that leads to the item array within your JSON structure. If you're unsure, use the JSON Path Language syntax to navigate through the nested objects. For example, if the item array is directly under the maintenance object, you could use maintenance.item.

2. Check Data Format:

Ensure that the data format for your source is set to "JSON". You can find this setting in the "Source" transformation properties under the "Format" tab.

3. Use Single Quote for Path:

Make sure you're using single quotes around the JSON root path value. Double quotes might not be interpreted correctly.

4. Consider Alternative Methods:

If the JSON root path approach doesn't work, you can try using a different transformation like "Data Flow Expression" before the "Flatten" transformation. In the "Data Flow Expression" transformation, you can use an expression to extract the item array and store it in a new column. Then, use this new column as the input for the "Flatten" transformation.

Double-check your JSON structure to ensure you're targeting the correct path to the item array.

Refer to the ADF documentation for more details on the "JSON root path" setting and data flow expressions: <invalid URL removed>

If you're still facing issues, provide more details about your JSON structure and any error messages you encounter for further assistance.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-02-07T07:05:12.83+00:00

@SWFollowing up to see if the above answer was helpful. 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.
SW 20 Reputation points

2024-02-07T10:19:41.6166667+00:00

Thanks Phemanth. I am still working on it now.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-02-08T09:53:09.3+00:00

@SW
Following up to see if the above answer was helpful. 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.

Share via

Flatten SOAP api JSON file

1 answer

Your answer