How to flatten the nested object from the array

Question

I have the data below

{    
  "value" : [
                  {
                    "id" : "123",
                     "name" : "testname",
                      "properties" : {
                                               "displayName" : "testname"
                                             }
                  },
                  {
                    "id" : "8459",
                     "name" : "testname2",
                      "properties" : {
                                               "displayName" : "testname2"
                                             }
                  }
                ]
}

I want to flatten the above data to below format:

{    
  "value" : [
                  {
                    "id" : "123",
                     "name" : "testname",
                      "displayName" : "testname"                              
                  },
                  {
                    "id" : "8459",
                     "name" : "testname2",
                     "displayName" : "testname2"                    
                  }
                ]
}

I tried data flow But was not able to do it.

Could you please let me know How we can do this?

Answer 1

Hi @Shekhar Nadide ,

Apologies for delay in response . I get what you are saying. It seems you want to store the output as an array, not as the object of the JSON.
To achieve that, before sink transformation , you can add an aggregate transformation and use collect function to convert object of JSON to array.

This is how the output looks like before converting to array.

Now, I added an aggregate transformation and used this expression collect(@(id=id,name=name,displayName=displayName))

It would convert the JSON objects into an array . This is the output after aggregation:

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Hi @Shekhar Nadide ,
Thankyou for using Microsoft Q&A platform and posting your query.
As I understand your question here, you want to transform the JSON data by flattening it to get the above mentioned desired output.

I reproduced your scenario by creating the dataflow . Providing the steps to achieve the desired result here:

1. In the source transformation , make sure you have selected 'Single document' as the JSON setting
2. Use flatten transformation and unroll by Value[] and select id,name and properties in the source column option
3. Use Derived column transformation to create a new column called 'displayName' and in the expression provide properties.displayName
4. Use Select transformation to unselect the properties column.
5. Use Sink transformation and in Sink settings, select FileName option as 'Output to Single file' and provide the outputfileName . Also, make sure In optimize tab, Partition option is selected as 'Single partition'
6. Call the dataflow in a pipeline and execute it.

Attaching video for your reference:

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators