How to unroll Json and Cosmos multiple nested Arrays in a single document and load dynamically into SQL using ADF

Question

How to unroll Json and Cosmos multiple nested Arrays in a single document and load dynamically into SQL using ADF

Shirish Reddy G 1

We have two sources in the form of JSON and COSMOS. We have multiple sub arrays as nested which we need to unroll them and load each nested array into separate table based on the values more or less like Parent-Child Hierarchy. How can we achieve this using Azure Data Factory, as we cannot unroll the multiple arrays unless we configure them, and we can only unroll one array at once using Flatten Transformation. Is there any possibility? If yes, Please help us with the approach to follow.

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-11-22T23:22:11.667+00:00

Hello @Shirish Reddy G ,

Welcome to the MS Q&A platform.

Yes, you cannot unroll multiple arrays in a single Flatten transformation. If you have multiple nested arrays to unroll, you can use multiple flatten transformations to unrolll.

Ex:
In the below screenshots, I have a JSON with two nested arrays. Using two flatten transformations, I can able to flatten both Nested arrays to tabular format. I hope this helps.
Please let me know if you have any further questions.
Shirish Reddy G 1 Reputation point

2022-11-23T01:59:20.483+00:00

Hi BhargavaGunnam-MSFT,

Thanks for your response.

The Provided answer can be applied when we know the count of arrays to be unrolled and here, we might be aware of number of flatten transformations to be used.

In our case, we do not have the list or number of nested sub arrays available and even if we fetch the list through some lookup, the ask is, how can we pass the name of the arrays dynamically (expecting to utilize expressions) to get flattened.

For example, in my file, Currently I have 50 sub arrays nested, and in coming days the count of the arrays may increase or decrease. The source document is not stable. It keeps changing., along with its child elements.

So, we are looking for a dynamic configuration if any, that would identify the arrays to be unrolled for each document (JSON/COSMOS) and load into destination, without fixed mapping.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-11-24T20:07:46.03+00:00

Hello @Shirish Reddy G ,
Thank you for the details. I doubt we can dynamically pass the name of the arrays in this case. But I can check with my internal team and get back to you with more details.
I appreciate your patience.

1 answer

Your answer

Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-11-22T23:22:11.667+00:00

Hello @Shirish Reddy G ,

Welcome to the MS Q&A platform.

Yes, you cannot unroll multiple arrays in a single Flatten transformation. If you have multiple nested arrays to unroll, you can use multiple flatten transformations to unrolll.

Ex:
In the below screenshots, I have a JSON with two nested arrays. Using two flatten transformations, I can able to flatten both Nested arrays to tabular format. I hope this helps.
Please let me know if you have any further questions.
Shirish Reddy G 1 Reputation point

2022-11-23T01:59:20.483+00:00

Hi BhargavaGunnam-MSFT,

Thanks for your response.

The Provided answer can be applied when we know the count of arrays to be unrolled and here, we might be aware of number of flatten transformations to be used.

In our case, we do not have the list or number of nested sub arrays available and even if we fetch the list through some lookup, the ask is, how can we pass the name of the arrays dynamically (expecting to utilize expressions) to get flattened.

For example, in my file, Currently I have 50 sub arrays nested, and in coming days the count of the arrays may increase or decrease. The source document is not stable. It keeps changing., along with its child elements.

So, we are looking for a dynamic configuration if any, that would identify the arrays to be unrolled for each document (JSON/COSMOS) and load into destination, without fixed mapping.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-11-24T20:07:46.03+00:00

Hello @Shirish Reddy G ,
Thank you for the details. I doubt we can dynamically pass the name of the arrays in this case. But I can check with my internal team and get back to you with more details.
I appreciate your patience.

Answer 1

Bhargava-MSFT 31,361 Microsoft Employee Moderator

Hello @Shirish Reddy G ,
Sorry for the delayed response. Here are the details.
Since the schema is changing, we can't perform flatten on a moving schema.
For dynamic names, use "Rule-based mapping" instead of fixed mapping. You can use regex and wildcards there as well as "Deep column traversal" to expand all nested elements.
A similar thread has been discussed here.
Please let us know if you have any further questions.

Shirish Reddy G 1 Reputation point

2022-11-29T04:09:11.96+00:00

Hi BhargavaGunnam-MSFT,

Thanks for the response. I shall check this way, if it can pick all the nested elements by passing each Array through loop.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-11-29T23:20:01.6+00:00

sure, thank you, @Shirish Reddy G

Share via

How to unroll Json and Cosmos multiple nested Arrays in a single document and load dynamically into SQL using ADF

1 answer

Your answer