cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
powerangli
New Member

How do I convert a JSON array to the desired format

Hi, everyone, I have a problem :

   now I have an arry like this:

powerangli_1-1648428423496.png

 

How to convert like that, Group by "Email", and join the "Topic":

[{"Email":"A","Topics":"A1,A2,A3……"},{"Email":"B","Topics":"B1,B2,B3……"}……]

 

Please help me, Thanks!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Mira_Ghaly
Dual Super User
Dual Super User

@powerangli 

1. Initial array represents your JSON output

Mira_Ghaly_0-1648437099324.png

2. Then I have A select Data Operation to select only the Email from your initial array , then initialized an array to get the unique emails as below:

Mira_Ghaly_1-1648437166312.png

Left Hand Side of the Select Expression:

item()['Email']

Union Expression of the Unique Emails array

union(body('Select'),body('Select'))

3. Next I have initialized final array for the last json representation and Topics string to hold the concatenation of the topics per email

Mira_Ghaly_0-1648437348028.png

4. Then Loop on the Unique Emails Array and Filter Initial array to get the topics per each email

Mira_Ghaly_1-1648437413523.png

Left Hand Side of the Filter Array:

item()?['Email']

Right Hand Side of the Filter array

items('Apply_to_each')?['Email']

5. Next is to to another apply to each loop on the filter array to get the topics per each email

Mira_Ghaly_2-1648437549970.png

Expression for the Append Array Variable:

concat(items('Apply_to_each_2')?['Topic'],',')

6. After constructing the Topics in the Apply to Each 2 , add a compose data operation and append the output from the compose data operation to the final array and clear the Topics variable

Mira_Ghaly_3-1648437621282.png

 

 

Expression for the Compose:

Email:

items('Apply_to_each')?['Email']

Topics:

substring(variables('Topics'),0,sub(length(variables('Topics')),1))

This is how the final array will look like:

Mira_Ghaly_4-1648437997164.png

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

View solution in original post

2 REPLIES 2
Mira_Ghaly
Dual Super User
Dual Super User

@powerangli 

1. Initial array represents your JSON output

Mira_Ghaly_0-1648437099324.png

2. Then I have A select Data Operation to select only the Email from your initial array , then initialized an array to get the unique emails as below:

Mira_Ghaly_1-1648437166312.png

Left Hand Side of the Select Expression:

item()['Email']

Union Expression of the Unique Emails array

union(body('Select'),body('Select'))

3. Next I have initialized final array for the last json representation and Topics string to hold the concatenation of the topics per email

Mira_Ghaly_0-1648437348028.png

4. Then Loop on the Unique Emails Array and Filter Initial array to get the topics per each email

Mira_Ghaly_1-1648437413523.png

Left Hand Side of the Filter Array:

item()?['Email']

Right Hand Side of the Filter array

items('Apply_to_each')?['Email']

5. Next is to to another apply to each loop on the filter array to get the topics per each email

Mira_Ghaly_2-1648437549970.png

Expression for the Append Array Variable:

concat(items('Apply_to_each_2')?['Topic'],',')

6. After constructing the Topics in the Apply to Each 2 , add a compose data operation and append the output from the compose data operation to the final array and clear the Topics variable

Mira_Ghaly_3-1648437621282.png

 

 

Expression for the Compose:

Email:

items('Apply_to_each')?['Email']

Topics:

substring(variables('Topics'),0,sub(length(variables('Topics')),1))

This is how the final array will look like:

Mira_Ghaly_4-1648437997164.png

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

Helpful,thank you very much!

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (1,828)