Filter JSON Array on Subarray with Power Automate

Question

I have a Power Automate flow which returns an array of results from a SharePoint list. One of the fields (Stops) is a JSON array stored as a string. Using some schema manipulation, I am able to transform my results to the following stored as the output of a Filter Array action called Create TripsArray

[
{
    "Trip": "My first Trip",
    "Stops": [
        {
        "Location": "New York",
        "Food": "Good"},
        {
        "Location": "California",
        "Food": "Bad"
        }
    ]
},
{
    "Trip": "My second Trip",
    "Stops": [
        {
        "Location": "California",
        "Food": "Good"},
        {
        "Location": "Washington",
        "Food": "Bad"
        }
    ]
},
{
    "Trip": "My Last Trip",
    "Stops": [
        {
        "Location": "Washington",
        "Food": "Good"},
        {
        "Location": "Rhode Island",
        "Food": "Bad"
        }
    ]
}
]

I would like to run a Power Automate flow to create a dynamic array of Places (strings) and return only those objects where one of the Stops was in a Place.

example:

TargetPlaces: ['Washington', 'Rhode Island']

expected return: [{"Trip": "My second Trip"}, {"Trip": "My Last Trip"}]

Ultimately, I would like to count how many Trips are returned, but I know I can do that with length()

CoPilot has suggested using intersection() and I've tried

intersection(body('Create_TripsArray')?['Trip']?['Stops']),outputs('Compose_TargetPlaces'))

error: 'Trip' cannot be selected. Array elements can only be selected using integer index.

Specific documentation suggestions with links would be welcomed!

tdy · Accepted Answer · 2025-06-18 03:49:17Z

1

If you're going to be providing the value to check for (i.e. "Washington"), you could use a filter action and join the values of the stops to see if they contain that value. The filter action would still return your full (but filtered) array of records.

For your filter action input, enter your array of data.
For the filter query,
join(item()?['Stops'],',') contains 'Washington'

edited Jun 18 at 3:49

tdy

42k42 gold badges124 silver badges125 bronze badges

answered Jun 18 at 1:58

user30829544

111 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filter JSON Array on Subarray with Power Automate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related