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.

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

  1. 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.

  2. Hello Pieter
    Thanks for the content and insight you provide.
    Regarding this bit that you wrote “In our example it is not necessary to handle multiple items in the second array. So we are lucky”.
    What if one is doubly lucky and has to handle multiple items in the second array? 🙂

    I have a quite similar situation and I have tried sooo many approaches but can’t isolate the different items in the apply to each from the second array.
    Both compose or append to string, used in different locations within nested apply to each, result in each individual record in one array receiving ALL the values from the second array that I would like to harvest information from; not just the matching values I would like.
    Do you have any posts/material/input you could point me towards that might help?
    Would be much appreciated.

  3. Pieter
    Thanks for the insight you provide.
    Regarding your statement “In our example it is not necessary to handle multiple items in the second array. So we are lucky”. What if one is doubly lucky and has to handle multiple items in the second array?

    I have a quite similar situation and I have tried sooo many approaches but can’t isolate the different items in the apply to each from the second array.
    Both compose or append to string, used in different locations within nested apply to each, result in each individual record in one array receiving ALL the values from the second array that I would like to harvest information from; not just the matching values I would like.
    Do you have any posts/material/input you could point me towards that might help?
    Would be much appreciated.

    1. Hi Joseph,

      The easiest thing tondo is use a filter action to get the items from your 2nd array that you want to link with. Then you can also double check in the flow run that the right items are returned. Then use the output from the filter action to links things up.

      1. Apologies for the earlier double post. And thanks for taking time to respond.

        Using Filter does not seem to work. I realise I did not describe my problem accurately enough. In my situation, the second array key/value pairs also contain a nested array. So using your example, the thread keys would each contain their own arrays.
        And this deeper/3rd level array values are what I would like to bring to the top and combine with the top/first level array, but no matter what I try those values in the 3rd level array always come out as a total package.
        So, if thread keys 1, 2, 3, 4 (in your second array) each contained their own key value pairs (with example a “thread value” value) with respective values of e.g. a, b, c, d for each item, I am only able to get an end result of {“id”:1,”data”:2,”code”:3,”thread value”: [a, b, c, d]”} for each ID in the first array ad not the desired result like {“id”:1,”data”:2,”code”:3,”thread value”:b}.

        I would have thought it possible, in an apply to each, to reset/stop appending values from the 3rd level array once the 2nd level array in an apply to each has completed each iteration for each 2nd level array item. But that does not seem to be how it works. Appending strings and also Compose seem to just add every value at the third level iteration, without being able to tag which record in the 2nd level array was being iterated. Sorry if none of this makes sense.
        But if you can add anything else that would be great.

      2. I would think in 2 steps.

        1. Massage the data. In this step reshape the data so that it looks like how you want it. You could use the Pieter’s method mentioned in the pot to pull out the nested array.

        Once you have your data right, then and only then consider using it to get to the end goal of using the data.

        These things can be really complicated but everything is possible.

      3. Thanks Pieter. I will have a retrace steps and see if an alternative approach e.g. the Pieter’s method 🙂 can perhaps be used earlier in the Flow to give me the data structure I need to work with later in the Flow.

  4. I get the error

    Flow save failed with code ‘InvalidTemplate’ and message ‘The template validation failed: ‘The inputs of template action ‘Filter_array’ at line ‘1 and column ‘7663’ is invalid. Action ‘Apply_to_each’ must be a parent ‘foreach’ scope of action ‘Filter_array’ to be referenced by ‘repeatItems’ or ‘items’ functions.’.’.

Leave a Reply

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

%d bloggers like this: