cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cmsamo
Frequent Visitor

Extract and process emails from JSON

I have a JSON structure in a sharepoint list that I need to process to get the email addresses from:

{
"Approver1": {
"Email": "bob@smith.com; dave@smith.com; ed@smith.com" ,
"DisplayName": "Bob;Dave;Ed"
},
"Approver2": {
"Email": "Jane@smith.com; carol@smith.com;dan@smith.com" ,
"DisplayName": "Jane;Carol;Dan"
},
"Approver3": {
"Email": "dan@smith.com;bob@smith.com; dave@smith.com; ed@smith.com" ,
"DisplayName": "Dan"
},
"Approver4": {
"Email": "" ,
"DisplayName": ""
},
"Approver5": {
"Email": "" ,
"DisplayName": ""
}
}

 

Desired result: "bob@smith.com;dave@smith.com;ed@smith.com;carol@smith.com;jane@smith.com;dan@smith.com"

 

I want to parse this JSON so that I end up with a single string of unique emails, and an array I can iterate around to send Teams notifications to. I tried using a JSON parse to get the emails into an array, and then a union to remove duplicates, and an array filter operation to remove empty entries, but it doesn't work since I don't think I am using the parsed JSON correctly to get the emails. How should I parse the structure to get what I need?

 

I have tried to split the Email objects using Split(Email, ';') with the aim of building an array of each individual email address, and then combining the arrays into one single array before doing a union and filtering to remove any empty strings that were processed from the JSON, but I end up with an array of arrays, and the union / removal of empty items does not work. 

1 ACCEPTED SOLUTION

Accepted Solutions
abm
Super User
Super User

Hi @cmsamo 

 

Please see your email.

 

So the steps I used are

 

1. Parse JSON to extract each approvers emails.

2. Concat into one string

3. Split emails using semi-colon

4. Finally use union() expression to get distinct values.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

View solution in original post

5 REPLIES 5
abm
Super User
Super User

Hi @cmsamo 

 

Please see your email.

 

So the steps I used are

 

1. Parse JSON to extract each approvers emails.

2. Concat into one string

3. Split emails using semi-colon

4. Finally use union() expression to get distinct values.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
cmsamo
Frequent Visitor

Thanks for the guidance. I'm almost there. The emails are all being combined with no semi-colon between them when I parse the JSON. I think I need to use concat(body('Parse_JSON')?['Approver4']['Email'],';') in the 'get email addresses' stage to ensure there's a semi colon if there's only one email address per stage. 

Hi @cmsamo 

 

Finally result is an array so what you could do is iterate the results and use a concat with semi-colon to construct the string you want. 

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
cmsamo
Frequent Visitor

Thanks ABM for the help. 

 

For anyone else - I was missing the correct way to get the email fields from my JSON, and not combining the strings properly or setting variables in the flow. 

 

1. Get the body data - concat(body('Parse_JSON')?['Approver1']['Email'],';')

2. Add the emails to a string using a Compose action - concat(variables('TeamsEmailString'),outputs('GetStage1ApproverEmails'))

3. Set the string as the result of the compose action in Step 2. 

4. Split my final email string into an array of the individual emails - split(variables('TeamsEmailString'),';')

5. Do a union on the array element in step 4 to remove duplicates

6. Do an array filter operation to remove empty email addresses

 

 

sjorusb2
New Member

Can someone post the raw flow so I can try to implement the same fix?

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (4,087)