Replace array items with key value pairs

[UmbrellaSpin]

I have an array of email addresses, some email addresses need to be substituted for a newer email address via key-value pairs before I can process them.

The array of email addresses is around 1900 in size. The key value pairs are around 50 items. I've tried the apply-each route but I'm hitting warnings for running times. I've tried SELECT action and a FILTER ARRAY action but I'm not having any success.

I've tried Googling but seems like my Googlefoo isn't working 😥

Thanks in advance for any help.

Sample below (note, data below was generated by mockeroo)

The array looks like this:

[
 "dandriesm@1und1.de",
 "wmorcombe6v@scientificamerican.com",
 "maddioniziop@archive.org",
 "ltivnan34@over-blog.com",
 "astaresmearet@weibo.com",
 "skassel7g@webmd.com",
 "avanderwalt1v@mit.edu",
 "fommanney7q@blogs.com",
 "mgatley9m@feedburner.com",
 "eweldon4v@bigcartel.com",
 "flammas1w@mac.com"
]

The key value pairs looks like this:

[
 {
 "oldEmail": "ltivnan34@over-blog.com",
 "newEmail": "xxxxxxx@xxxxx.com"
 },
 {
 "oldEmail": "mgatley9m@feedburner.com",
 "newEmail": "yyyyyyy@yyyyyyy.com"
 }
]

The result array I would like is:

[EDIT] I made a mistake which is now corrected and in bold.

[
 "dandriesm@1und1.de",
 "wmorcombe6v@scientificamerican.com",
 "ltivnan34@over-blog.com",
 "xxxxxxx@xxxxx.com",
 "astaresmearet@weibo.com",
 "skassel7g@webmd.com",
 "avanderwalt1v@mit.edu",
 "fommanney7q@blogs.com",
 "mgatley9m@feedburner.com",
 "yyyyyyy@yyyyyyy.com",
 "eweldon4v@bigcartel.com",
 "flammas1w@mac.com"
]

[DJ_Jamba]

Hi @UmbrellaSpin 

You need 2 select actions.
Using your array examples above:

Create 2 select actions (make sure they are both pulling from array2).
Use text mode to perform the map.

First select maps oldEmail:

Second select maps newEmail:

Then use a compose to union all 3 arrays:

union(outputs('Array1'),body('Select_old_email'),body('Select_new_email'))

Result:

[
  "dandriesm@1und1.de",
  "wmorcombe6v@scientificamerican.com",
  "maddioniziop@archive.org",
  "ltivnan34@over-blog.com",
  "astaresmearet@weibo.com",
  "skassel7g@webmd.com",
  "avanderwalt1v@mit.edu",
  "fommanney7q@blogs.com",
  "mgatley9m@feedburner.com",
  "eweldon4v@bigcartel.com",
  "flammas1w@mac.com",
  "xxxxxxx@xxxxx.com",
  "yyyyyyy@yyyyyyy.com"
]

[UmbrellaSpin]

 @DJ_Jamba thank you very much for your swift reply. Sorry I made a mistake in the expected output.

I'm looking for the old email to map to the new email. I hope this image gives a better idea what I'm after.

[v-yetonggu-msft · Accepted solution]

Hi @UmbrellaSpin ,

Do you want to replace the old email address with the new email address in an array?

I think I see what you mean, please see the results:

My Flow Overall Preview:

Note that my 'Apply to each' and 'Apply to each 2' are two separate loops, no nesting, 'Compose 4 'is outside of 'Apply to each 2'.

My Flow Configuration Parameters:

item()?['oldEmail']
length(body('Filter_array'))
length(outputs('Compose_2'))

items('Apply_to_each_2')?['newEmail']

Best Regards,

Sunshine Gu

[ManishSolanki]

Hi @UmbrellaSpin 

Alternatively, you can get the final array in few actions with the help of XML & XPATH functions. Here are the steps:

I have used compose action to store both input arrays:

[
"dandriesm@1und1.de",
"wmorcombe6v@scientificamerican.com",
"maddioniziop@archive.org",
"ltivnan34@over-blog.com",
"astaresmearet@weibo.com",
"skassel7g@webmd.com",
"avanderwalt1v@mit.edu",
"fommanney7q@blogs.com",
"mgatley9m@feedburner.com",
"eweldon4v@bigcartel.com",
"flammas1w@mac.com"
]

[
{
"oldEmail": "ltivnan34@over-blog.com",
"newEmail": "xxxxxxx@xxxxx.com"
},
{
"oldEmail": "mgatley9m@feedburner.com",
"newEmail": "yyyyyyy@yyyyyyy.com"
}
]

Add another compose action to convert output of compose 2 action in xml format. We will use expression to do the conversion. Expression needs to be entered in the expression box as shown below:

xml(json(concat('{"root":{"items":',outputs('Compose_2'),'}}')))

Finally, add "Select" action to get the required array. Pass the output of compose action as its input in 'From' parameter:

Click 'Switch Map to text mode' button:

Enter the below expression in the Map textbox:

if(empty(xpath(xml(outputs('Compose_3')),concat('string(//items[oldEmail="',item(),'"]/newEmail/text())'))),item(),xpath(xml(outputs('Compose_3')),concat('string(//items[oldEmail="',item(),'"]/newEmail/text())')))

The output of select action is the required array.

Output:

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[DJ_Jamba]

@UmbrellaSpin  - OK I understand now.

You do not need to replace any emails, you simply need to remove emails that are in the Old Email array.

Everything stays the same except union array 1 and new emails array

union(outputs('Array1'),body('Select_new_email'))

Then you simply need to remove all of the old emails using a filter array action:

Result:

[
  "dandriesm@1und1.de",
  "wmorcombe6v@scientificamerican.com",
  "maddioniziop@archive.org",
  "astaresmearet@weibo.com",
  "skassel7g@webmd.com",
  "avanderwalt1v@mit.edu",
  "fommanney7q@blogs.com",
  "eweldon4v@bigcartel.com",
  "flammas1w@mac.com",
  "xxxxxxx@xxxxx.com",
  "yyyyyyy@yyyyyyy.com"
]