In my The advanced Pieter’s method in Power Automate post, I received a comment asking about joins when using json arrays.
The Data used
In this post I’ve assumed that you have read the above post.
As an example I have the following two arrays. Most likely you will find that your arrays are larger:
Array 1
[
{"id":1,"data":2,"code":3},
{"id":2,"data":3,"code":4},
{"id":3,"data":4,"code":5}
]
and array 2:
[
{"data":1,"thread":1},
{"data":2,"thread":2},
{"data":3,"thread":3},
{"data":4,"thread":4}
]
The result needs to be as shown below:
[
{"id":1,"data":2,"code":3,"thread":2},
{"id":2,"data":3,"code":4,"thread":3},
{"id":3,"data":4,"code":5,"thread":4}
]
Introduction to Joins
The world of data contains multiple types of joins
Looking at our data, then it looks like we have a left or an inner join. In our case data that exist in array 1 will always find a matching record in the second array. Looking at the definitions of the two join types, it looks like inner join is the one that seems most relevant.
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
The INNER JOIN keyword selects records that have matching values in both tables.
Inner Joins in Power Automate
I’ve built the following flow. Note the speed at which this is running. Just 1 second.
In this case I didn’t even enable the concurrency on the apply to each yet. When you are looking at larger data sets then you might have to consider doing so.
We need to have a look at the details of the flow.
Create arrays
To create the arrays I am using two compose actions. They are both a copy of my data as described earlier.
Filtering array 2
Now I’ll push the data into the Apply to each using the following expression in the Select an output from previous steps field.
outputs('Array1')
Now in the Filter array action the From is set to
outputs('Array2')
All there is left to do in the filter is set the filter to compare the data field from array 1 with the data field in array 2.
@equals(item()?['data'], items('Apply_to_each')?['data'])
Why?
Well, now we will only have the relevant item in the second array. to worry about. This 1 item we can now use in the Compose action in the Apply to each following the filter action.
If you have read the Pieter’s method post mentioned at the top of this post. Then you will understand that all we need to do is set the value of a compose inside the apply to each.
As shown below we use the following code:
{
"id": 1,
"data": 2,
"code": @{items('Apply_to_each')?['code']},
"thread": @{first(body('Filter_array'))?['thread']}
}
Note that we use the first function to turn a single item array into an object. In our example it is not necessary to handle multiple items in the second array. So we are lucky.
The Pieter’s method
Now all we need to do is, collect the array of items just after the apply to each step using a compose set to:
outputs('Compose')
This second compose action will now merge all the results of the Compose action inside the apply to each to a single array of objects that we want to get without taking any time at all.
And our flow is complete as our array is build as we want it.
Further Thoughts
When working with json in Power Automate, performance of flows can be very complicated. Until of course you find a solution that works. The example shown in this post will not be covering all join options, however it should help make any kind of join possible. If you need any help making your flows perform then please do not hesitate to contact me.
Pieter Veenstra
Is your business still running on paper trails, sprawling Excel files, or ageing Access databases? There’s a better way — and I can show you exactly what it looks like. I’m the Technical Director of Vantage 365, a Microsoft solutions consultancy working with clients across the UK, the Netherlands, and worldwide. For over 30 years I’ve been turning messy, manual business processes into clean, automated systems that save time, reduce errors, and give teams the visibility they need to make better decisions. SharePains is not just any blog run by a Microsoft MVP. Have you ever used Try-Catch in Power Automate? The original post about Try-Catch in Power Automate can still be found on this site, https://staging.sharepains.com/2018/02/07/try-catch-finally-in-power-automate-flow/ Or have you ever used the Pieter’s method to avoid variables and speed up your flows? https://staging.sharepains.com/2020/03/11/pieters-method-for-advanced-in-flows/ You can contact me using contact@staging.sharepains.com
Leave a Reply
You must be logged in to post a comment.