Create super fast joins of 2 arrays in Power Automate

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

• left joins
• inner joins
• right joins
• full join
• self join 

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.