creating joins

Create super fast joins of 2 arrays in Power Automate

On my The advanced Pieter’s method in Power Automate post, I received a comment asking about joins when using json arrays.

In this post I’ve assumed that you have read the above post.

The Data used

In the question I have the following two arrays:

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.

speedy solution for json joins in a flow

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.

Two arrays of json in Compose actions

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.

Filtering and collecting json arrays

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.

Final Compose action using Pieter's method

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.

2 thoughts on “Create super fast joins of 2 arrays in Power Automate

  1. commented on March 20, 2020 by Manish Jain

    Thanks it looks I am going right way !!

  2. commented on March 20, 2020 by Ed

    I’ve been doing something similar, but I’m having to work with a couple of arrays with over 1000 records, and my matches may be as few as 10 records. With an apply to each going through 1000+ records, my process was running over an hour.

    What I did to fix this was pare down the array being used in the apply to each.
    In this scenario, I would do a select on Array 2 and only select the “code” field. Then I do a join on that array, let’s call the result array2Str.
    From there I filter Array1 where array2Str contains item()?[‘code’]
    Then I take the filtered Array1 and do the apply to each.
    This reduced my run time from over 1 hr to just a few seconds.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: