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!
