Extract and process emails from JSON

[cmsamo]

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. 

[abm · Accepted solution]

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

[cmsamo]

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. 

[abm]

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

[cmsamo]

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]

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