Hi i am having json file like this these are the json objects in a single file Now in each object these are the fields now one fields Issue is an array field. now if i expand the issue again it is containing multiple array , How can we flatten this kind of json file in adf ?

Hi @Amar Agnihotri Thanks for using Microsoft Q&A forum and posting your query. I'm assuming that the structure remains consistent across the iterations. In such scenarios, you will have to start flattening from top to bottom level. You will have to use multiple subsequent flatten transformations as described in this thread: Unable to flatten a complex json object using mapping data flow flatten activity The referenced thread has similar requirement (slight difference in the hierarchy). For the above structure, you will have to use 3 flatten transformations. One for each for highlighted complex array objects. Your sample may look like this (Note this image from a difference requirement but it applies to your requirement as well) Hope this helps. Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

How to flatten json file having multiple nested arrays in a single array using mapping data flows ?

Amar Agnihotri 921

Hi i am having json file like this

these are the json objects in a single file

User's image

Now in each object these are the fields

User's image

now one fields Issue is an array field. now if i expand the issue again it is containing multiple array ,

User's image

How can we flatten this kind of json file in adf ?

1 answer

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2023-04-28T23:23:14.0833333+00:00

Hi @Amar Agnihotri

Thanks for using Microsoft Q&A forum and posting your query.

I'm assuming that the structure remains consistent across the iterations. In such scenarios, you will have to start flattening from top to bottom level. You will have to use multiple subsequent flatten transformations as described in this thread: Unable to flatten a complex json object using mapping data flow flatten activity

The referenced thread has similar requirement (slight difference in the hierarchy).

For the above structure, you will have to use 3 flatten transformations. One for each for highlighted complex array objects.

Your sample may look like this (Note this image from a difference requirement but it applies to your requirement as well)

Hope this helps.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.
Amar Agnihotri 921 Reputation points

2023-04-29T17:34:59.6533333+00:00

hi @KranthiPakala-MSFT

Thanks for the reply. I have tried the same approach but in the second flatten activity every array is disabled for me. See here

This is my first flatten activity in which i tried to unroll by issues[]

because in the hierarchy the first array is issues. Now i used another flatten activity assuming that i would be able to unroll the next object which is fields[] and this is inside the issues [] array.

i am talking about the below one

and see that everything is disabled here

Now i am stuck here and don't know what to do now .

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2023-04-30T16:00:51.44+00:00

@Amar Agnihotri - Thanks for getting back. Could you please help share a sample source file with dummy data so that I can try it out and share my findings.

Thank you.

Amar Agnihotri 921 Reputation points

2023-05-02T13:43:42.1466667+00:00

hi @KranthiPakala-MSFT

Please give me your email id . The file size is 37mb.

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2023-05-04T00:13:00.43+00:00

@Amar Agnihotri Thanks for your response. could you please send an email to AzCommunity[at]Microsoft[dot]com with the below details. Please let me know here once email is sent.

Email subject: <Attn - Kranthi : Microsoft Q&A Thread title>

Thread URL: <Microsoft Q&A Thread>

Sample data attached

Thanks

Amar Agnihotri 921 Reputation points

2023-05-04T11:34:24.96+00:00

@KranthiPakala-MSFT

Mail sent

It is sent from aagnihotri@solifi.com

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2023-05-04T21:28:35.21+00:00

@Amar Agnihotri - Thanks for sending the email with request details. Will take a look at it and come back to you once I have my findings ready.

We appreciate your patience.

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2023-05-06T00:20:00.3233333+00:00

@Amar Agnihotri

Using the source file which you have shared, I was able to flatten the json as below (Please note I have only focused on few of the array objects, as the size of single row is very huge):

In next flatten, I did as below:

And so on..

Please make sure that in the first flatten transformation you select the subsequent array objects so that you can use then in next flatten unroll root and unroll by properties. If you miss them to select in the mapping of first flatten transformation, then they won't be available in the subsequent flatten transformations.

Hope this info helps.

Amar Agnihotri 921 Reputation points

2023-05-06T07:46:52.8266667+00:00

Hi @KranthiPakala-MSFT

Thanks for the brief description. I tired the same way but still in the second flatten activity everything is disabled in unroll by section ..

In the first flatten activity i selected Issues array to unroll by

Now suppose i want to flatten only project category then i selected project category as

Now in the second flatten activity what i supposed to do is to flatten Project Category but in unroll by section i am not able to select ProjectCategory

Can you please tell what is still missing in the second flatten activity?

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2023-05-12T22:21:45.3966667+00:00

@Amar Agnihotri

From the sample data you have provided, I see that ProductCategory is not an array, hence it cannot be flattened. If you want to access the inner objects of your ProductCateogy you will have to select them explicitly while flattening your Issues array as shown below:

Hope this info helps.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2023-05-12T22:26:56.4766667+00:00

@Amar Agnihotri

Just an FYI : Instead of using multiple transformations, you can just use single flatten transformation and unroll multiple arrays as shown below:

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2023-05-15T14:33:05.8066667+00:00

@Amar Agnihotri

Just checking in to see if the above information was helpful. If it answers your query, please do click Accept Answer and Yes for "was this answer helpful", as it might be beneficial to other community members reading this thread. If you have any further query, do let us know.

Thank you
Sign in to comment