Power Automate

Compare/Filter two arrays containing different array structure but one common field

(1) Share

Report

Posted on by rw_ga

137

Hi all,

I'm trying to accomplish two tasks:

Find and upload the missing files from our website based on a list of files in our database.
1. The list of database images has more details because I'll have to use an apply to each on the filtered results to locate the original records to download the image that will then be SFTP'd to the website.
Delete files from our website that aren't listed in our database.

For task 1 I was trying to compare 2 select action outputs using a Filter Array action but the Filter Array action isn't working correctly. I've done something wrong because I get all the items listed from the 1st Select Actions output. Can anyone help me figure out what I'm doing wrong?

1st Select - List of Website Images

From - @{body('List_files_in_folder_-_SFTP_-_SSH_-_List_Images')}

Map - {

"Image File Name": "example.jpg"

}

2nd Select - List of Database Images

From - @{outputs('List_rows_-_Listings_-_Images')?['body/value']}

Map - {
"Listing ID": "1",
"Product Name": "Test",
"Image File Name": "example.jpg",
"Image File URL": "https://website.com/example.jpg",
"Table": "product_tbl",
"Row ID": "12345"
}

Filter Array - Images to be uploaded to website

From - body('Select_-_List_of_Database_Images')

Edit in advanced mode - @not(contains(body('Select_-_List_of_Website_Images'), item()?['Image File Name']))

The "Filter Array - Images to be uploaded to website" action's output always returns everything from the "Select - List of Database Images" output. I only want the output showing the entries from the database that aren't on the website.

Thoughts?

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi

Try the below.

body('Select_-_List_of_Website_Images')?['Image File Name']

Thanks

Was this reply helpful? Yes No
rw_ga 137 on at

Like (0)

Report

@abm abm

Hi,

Are you suggesting placing this "body('Select_-_List_of_Website_Images')?['Image File Name']' like below?

Filter Array - Images to be uploaded to website

From - body('Select_-_List_of_Database_Images')

Edit in advanced mode - @not(contains(body('Select_-_List_of_Website_Images')?['Image File Name'], item()?['Image File Name']))

The above causes the flow to fail with the following error:

The execution of template action 'Filter_array_-_Images_to_be_uploaded_to_Website' failed: The evaluation of 'query' action 'where' expression '@not(contains(body('Select_-_List_of_Website_Images')?['Image File Name'], item()?['Image File Name']))' failed: 'The template language expression 'not(contains(body('Select_-_List_of_Website_Images')?['Image File Name'], item()?['Image File Name']))' cannot be evaluated because property 'Image File Name' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

Thanks,

RW

Was this reply helpful? Yes No
Suggested answer

rw_ga 137 on at

Like (0)

Report

Okay I think I got it working with a slight modification to 1st Select - List of Website Images as follows and adding an additional Select action to complete the 2nd task.

Modification to 1st Select - List of Website Images

Switch Map to Text Mode then only collect the actual values of the file names, see below 'Peek Code' Example

    "inputs": {

        "from": "@body('List_files_in_folder_-_SFTP_-_SSH_-_List_Images')",

        "select": "@item()?['Name']"

This produces an array like below:

[

"example.jpg",

"example2.jpg",

"example3.jpg",

"example4.jpg"

]

Now change the Filter Array action to:

Filter Array - Images to be uploaded to website

From - body('Select_-_List_of_Database_Images')

Edit in advanced mode - @not(contains(body('Select_-_List_of_Website_Images'), item()?['Image File Name']))

'Peek Code' Example:

    "inputs": {

        "from": "@body('Select_-_List_of_Database_Images')",

        "where": "@not(contains(body('Select_-_List_of_Website_Images'), item()?['Image File Name']))"

    },

This produces output like below:

[
{
   "Listing ID": "89",
   "Product Name": "Test89",
   "Image File Name": "example89.jpg",
   "Image File URL": "https://website.com/example89.jpg",
   "Table": "product_tbl",
   "Row ID": "12389"
},
{
   "Listing ID": "54",
   "Product Name": "Test54",
   "Image File Name": "example54.jpg",
   "Image File URL": "https://website.com/example54.jpg",
   "Table": "product_tbl",
   "Row ID": "12354"
}
]

Now I should be able to use this output to download the Image from the database and use an apply to each to upload them to the web server.

To accomplish the 2nd task, I added a 3rd - "Select - Database File Names" to collect just the "Image File Name" from the database select action.

"Select - Database File Names"

From - body('Select_-_List_of_Database_Images')

Switch Map to Text Mode then only collect the actual values of the file names, see below 'Peek Code' Example

    "inputs": {

        "from": "@body('Select_-_List_of_Database_Images')",

        "select": "@item()?['Image File Name']"

    }

This produces an array like below:

[

"example.jpg",

"example2.jpg",

"example3.jpg",

"example4.jpg"

]

Now add another Filter Array action to compare the filenames in the 1st Select output and the 3rd output.

Filter Array - Images to be Deleted From website

From - body('Select_-_List_of_Database_Images')

Edit in advanced mode - @not(contains(body('Select'), item()))

"Peek Code" Example:

    "inputs": {

        "from": "@body('Select_-_List_of_Website_Images')",

        "where": "@not(contains(body('Select_-_Database_File_Names'), item()))"

    },

This filter's output should allow me to loop through and delete each file from the web server.

Hopefully this will help someone else attempting to do something similar and provide me notes for a future issue. :-)

Was this reply helpful? Yes No