Filter Array with another array

[DavidLienhard]

Dear Power Automate Community

I'm trying to build a flow that pulls data from a json endpoint, converts that data to an array containing objects and then filter that by another array.

So currently I do have the following to arrays:

Items:

[
  {
    "CVE": "CVE-2022-23943",
    "Description": "Out-of-bounds Write vulnerability in mod_sed of Apache HTTP Server allows an attacker to overwrite heap memory with possibly attacker provided data. This issue affects Apache HTTP Server 2.4 version 2.4.52 and prior versions.",
    "Score": 9.8,
    "Severity": "CRITICAL"
  },
  {
    "CVE": "CVE-2022-22721",
    "Description": "If LimitXMLRequestBody is set to allow request bodies larger than 350MB (defaults to 1M) on 32 bit systems an integer overflow happens which later causes out of bounds writes. This issue affects Apache HTTP Server 2.4.52 and earlier.",
    "Score": 9.8,
    "Severity": "CRITICAL"
  },
  ....
]

Keywords:

[
  "apache",
  "veeam"
]

 What I'd like to do is only keep the items in the "Items" array, that contain one or more of the keywords in the "Keywords" array.

I hoped this would be possible with the array filter functionality like this:

Where Description is item()?['Description']

This does not work though an throws the following error:

The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@contains(item()?['Description'], variables('keywords'))' failed: 'The template language function 'contains' expects parameters of matching types: a dictionary and a key (string), an array and a value (object), or a string and a substring. The provided types 'String' and 'Array' are incompatible. Please see https://aka.ms/logicexpressions#contains for usage details.'.

So it seems its not possible to filter an array by another array.

Is there a possibility to achieve this functionality?

Best regards

[gouteiro]

Hello @DavidLienhard,

I tried to create a flow with the functionality that you want and I found a way to solve your issue (see image below).

The Json array that I am receiving is the one below:

[ 
 { 
  "FirstName": "Eugenia", 
  "FamilyName": "Lopez", 
  "FullName": "Eugenia Lopez" 
 }, 
 {  
  "FirstName": "Elizabeth", 
  "FamilyName": "Moore", 
  "FullName": "Elizabeth Moore" 
 } 
]

In order to filter the elements in a Json array regarding the elements in another array, you have to do an 'Apply to each' cycle where you check if the current element of the array is contained in the string where do you want to search. Then, you can append the results of the filter action to a "final array", that will contain all the elements that you want. 

Is this solution good for you?

Looking forward to your response.

[DavidLienhard]

Hi @gouteiro 

Thanks for you reply. I did not bring this to work so far. May I ask whats the contant of the "body" Elemten of your Filter Array step?

Also wouldn't this add an element to the final array multiple-times if there would be multiple matches for keywords?

Best regards

[gouteiro · Accepted solution]

Hello @DavidLienhard,

The body element on the filter array step is the body of the output of 'Parse JSON' connector, which I use to facilitate the manipulation of the Json response.

Then, to make sure that a duplicate element is added to the final array, we can make a condition to verify if the element is already in the array (see image below), and if not, we add it.

Is this a good solution for you? I hope I could help!

Looking forward to your response!

If I have answered your question, please mark your post as Solved. If you like my response, please give it a Thumbs Up.

[DavidLienhard]

Hi @gouteiro

Sorry for my late reply, it took me some time to get into this.

I brought my flow to work with your help. Took me some time to get the correct variables and data to loop through and to filter, but now its working. It's pretty slow but I don't mind.

So many thanks for your help on this.

Best regards