Answered

Filter Array with another array

(0) Share

Report

Posted on by 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

I have the same question (0)

All responses (4)

Answers (1)

gouteiro Microsoft Employee on at

Like (1)

Report
Re: Filter Array with another array

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.

Was this reply helpful? Yes No
DavidLienhard 6 on at

Like (0)

Report

Re: Filter Array with another array

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

Was this reply helpful? Yes No
Verified answer

gouteiro Microsoft Employee on at

Like (1)

Report

Re: Filter Array with another array

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.

Was this reply helpful? Yes No
DavidLienhard 6 on at

Like (0)

Report

Re: Filter Array with another array

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

Was this reply helpful? Yes No