How to do an anti join?

[Imke]

Hi there,

I have 2 arrays and want to keep only those items from the first array, which are not included in the second array:

first array: [1, 2, 3]
second array: [3,4]

desired result: [1, 2]

Any help appreciated.

[Paulie78]

@DamoBird365 has a great method for this....

[Imke]

Cool, thanks for calling him in.
Just wanted to add that the number of items (both to keep and to skip) is variable.

[DamoBird365 · Accepted solution]

Hi @Imke 

@Paulie78 thanks for the shout out!  

I've just written a blog post on exactly that, it's like you knew!  You can take a look here where I cover, Union, Except and Intersect.  It sounds like in this case you are looking to perform an Except and return only the items that are unique to the first array.  As there is no in-built expression for this you can use a select action and compare your first array with your second with each item, all in one action! Alternatively you can use an apply to each on the first array and systematically work through the second array checking to see if a value exists, this is rather time consuming and a significant overhead on your Cloud Flow.

Here is an example of an except where list2 items don't appear in list1 (which is simply a reverse of your requirement).  Please take a look at my blog post and video and let me know how you get on.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[Imke]

Ha, ha - yes, that's almost what I just tried: 
@not(contains(outputs('Array2'), item()))
Thanks!!

[Imke]

Your article was probably too new to be picked up by my search machine 🙂
Very good!

[DamoBird365]

Hi @Imke 

I had to look at your profile join date as I couldn't believe that this had popped up today.  Very amusing and grateful to have the opportunity to help you.

Cheers,

Damien