This post describes how to merge two separate arrays that contain a common value. My friend Tom Riha recently wrote a good post on how to do this. His method will work well for a small dataset, but for a large array it would be quite time consuming as it uses an Apply to each loop.
I also did a video on this subject in response to the a question on the Power Automate Forums:
The Example Data
We are going to combine two arrays, here is the first one:
[ { "Name": "Tom", "Country": "CZ" }, { "Name": "Paul", "Country": "UK" } ]
The second array:
[ { "Name": "Tom", "Age": "younger than Paul" }, { "Name": "Paul", "Age": "older than Tom" } ]
The combined array we want to create:
[ { "Name": "Tom", "Country": "CZ", "Age": "younger than Paul" }, { "Name": "Paul", "Country": "UK", "Age": "older than Tom" } ]
In order to combine these into a single array, we need a common value in each array. In this example the Name key is common to both arrays. We are going to use the powerful xpath expression to perform this in a single step.
Array Preparation
Before we can use xpath, the second array needs to be converted to XML, and before we can do that, we need to do some preparation. Check out this screenshot:

All of the above actions are Compose actions. The step named PrpareArray2 simply wraps the output of Array2 around some additional JSON and produces the following output:
{ "Root": { "Array": [ { "Name": "Tom", "Age": "younger than Paul" }, { "Name": "Paul", "Age": "older than Tom" } ] } }
Now that it is in the proper format the step named XML converts the array to XML and produces the following output:
<Root> <Array> <Name>Tom</Name> <Age>younger than Paul</Age> </Array> <Array> <Name>Paul</Name> <Age>older than Tom</Age> </Array> </Root>
Combine the Arrays
With all of that prep work done, we can begin to actually merge the arrays. See this Select action:

As you can see, all of the work is done in a single select action, but it requires a bit of explanation. The first part is straightforward:
- The input for the select is Array1
- The Keys Name and Country are drawn from Array1 using the item() syntax.
- The Age key is pulled from the XML using the xpath expression.
The xpath expression is easier to read like this:
xpath ( outputs('XML'), concat('//Array[Name/text()="', item()['Name'], '"]/Age/text()') )?[0]
The concat function is used to dynamically create an xpath query by using the name from the current item. So in the first iteration of the loop the xpath expression would be:
//Array[Name/text()="Tom"]/Age/text()
What are we asking xpath to do here:
- Find all of the elements within Array
- Only find nodes within Array where Name is equal to Tom
- Grab the text value of the Age where the Name matched.
You can test this out for yourself by copying the XML above and pasting it into xpather.com – then copy the expression above and you should see results like this:.

xpath will return an array of results, even if there is only a single match, so the final part of the expression is: [0] to grab the first result of the array.
So in a single select action we can combine the arrays easily. In our example we could simply it further by using the addProperty function in the select instead of key/value pairs.

In this example the Select action was changed to text mode instead key/value mode. Then I used the expression:
addProperty ( item(), 'Age', xpath(outputs('XML'), concat('//Array[Name/text()="', item()['Name'], '"]/Age/text()'))?[0] )
This simply takes the current item and adds a new property called Age and assigns the value returned from the xpath expression.
Conclusion
Which method should you use and is this method worth the hassle?
It depends on how performance dependant your flow is. If you are only working with a small amount of data, the method Tom shows is easier and will execute quickly.
If you are working with hundreds or thousands of rows of data, then this method will be much faster. It’s always fun to compare different methods of doing the same thing!
Evolvous says
Great Share! Thank you for Sharing.
Max says
You definitely need a „like“-button on your site.
Thanks!
Cyril says
Nice post.
Another xpath tester / generator: https://extendsclass.com/xpath-tester.html
Somni says
Hello,
Thank you very much for all you do. It helps a lot. But this time, I don’t understand why I’ve got an error: ” ‘The template language expression ‘xpath(outputs(‘XML’),concat(‘//Array[ID/text()=”‘,item()[‘ID’],'”]/Seuil/text()’))[0]’ cannot be evaluated because array index ‘0’ cannot be selected from empty array. ” (Instead of “name” like in your example, I’m using an ID so I followed your video).
The thing is my array is from a spreadsheet (Excel online) that I put in a variable array….Is that the problem? If so, what can I do? I need to join 2 spreadsheed but I had some “distinct” and “sum” to do with the first one before joining the 2 sheets together.
Somni says
OK, so I tested different scenarios and it seems like when I have more than 3 items in the array1, it doesn’t work. When I juste have 3 items, it works perfectly. How can I do to merge when I have 106 lines in the array1 ?
Somni says
Me again, sorry if I “spam” you. You can delete all my comments. I found out where the problem was: I had more ID on array1 than array 2. The error message was not very explicit…..
David Haygood says
Hi Somni,
I experienced the same issue, but when I added a ‘?’ before the index it worked. So try this…Seuil/text()’))?[0]
Hi Paulie, thank you so much for your great time saving logic. It is truly appreciated.
Katrina says
Love your solutions, would it be possible to post an example using sharepoint lists/update item?
Pablo says
@David Haygood thanks for your comment, got my flow working! What does the ? do in that case?
Jason says
Thank you for documenting and sharing this approach, it’s exactly what i wanted to achieve.
Emmanuel says
Great post Paul! Would it also work if you used First function like this?
First(xpath(outputs(‘XML’),concat(‘//Array[ID/text()=”‘,item()[‘ID’],'”]/Size/text()’)))
Elizabeth says
This is great! Excellent video and article. A true gem.
robert says
This is createing a left outer join, how can we change this to create an inner join, so only select the item that matches one in the array 2?
Ricardo Bunge says
Paulie, this is fantastic — have used it several times with great success. Have a question about a slight variation.
Have two arrays, but they don’t contain objects, just concatenated text. For instance,
[
“john, apple, 6”,
“mary, pear, 10”
]
and
[
“john, blue”.
“mary, purple”
]
the john and mary bits will always be unique. I’m looking to merge the two matching on the name, so the resulting array will be as follows:
[
“john, apple, 6, blue”,
“mary, pear, 10, purple”
]
is this possible?
Paulie says
Totally possible.