ADF filter activity JSONPath syntax for the items property

Question

I retrieve some JSON due to previous paging in REST APIs with multiple array elements in which there is content that I would like to filter. The JSONPath to do so would be $.value[*].data, but the filter activity's specification for the value seems not to allow for a wildcard in value. Below you find the kind of JSON that is returned by the Lookup activity (called 'Lookup Respondents'):

{
    "count": 2,
    "value": [
        {
            "data": [
                {
                    "uuid": "bdc0b51c-9888-4a2c-831d-744edb55d9fb",
                    "status": "open"
                },
                {
                    "uuid": "1a79036b-84c7-4232-8585-31be3775d95c",
                    "status": "closed"
                }
            ],
            "pagination": {
                "currentPage": 1,
                "totalCount": 4,
                "nextPage": 2               }
        },
            "data": [
                {
                    "uuid": "8da907f6-b8a5-4119-b671-1be501e83bf0",
                    "status": "closed"
                },
                {
                    "uuid": "d0faf159-a712-47db-a7ab-83e44591a2c6",
                    "status": "open"
                }
            ],
            "pagination": {
                "currentPage": 2,
                "totalCount": 4         }
        }
    ],
    "effectiveIntegrationRuntime": "SelfHostedIR-prd(SelfHostedIR-prd)",
    "billingReference": {
        "activityType": "PipelineActivity",
        "billableDuration": [
            {
                "meterType": "SelfhostedIR",
                "duration": 0.016666666666666666,
                "unit": "Hours"
            }
        ]
    },
    "durationInQueue": {
        "integrationRuntimeQueue": 7
    }
}

So part of the ADF source I would like to use

            "name": "Filter respondents",
            "type": "Filter",
            "dependsOn": [
                {
                    "activity": "Retrieve respondents",
                    "dependencyConditions": [
                        "Succeeded"
                    ]
                }
            ],
            "userProperties": [],
            "typeProperties": {
                "items": {
                    "value": "@activity('Retrieve respondents').output.value[*].data",
                    "type": "Expression"
                },
                "condition": {
                    "value": "@equals(item().status,'closed')",
                    "type": "Expression"
                }
            }

As previously indicated the wildcard in output.value[].data* is not allowed. output.value[0].data is working, but obviously misses records in the filter. Any suggestions how to approach this challenge?

Answer 1

Hi @Jan Cirpka ,
Welcome to Microsoft Q&A platform and thanks for posting your query in the forum.
As I understand your query, you are trying to filter out the data for which status parameter is 'closed' after the lookup activity returns the Output JSON .
The challenge here is that the value array is having data field which is also an array . You want to check the value of status parameter in each of the data fields. This can't be done using filter activity as it doesn't support wildcard. Also , we need to flatten the output JSON first in order to filter the underneath data.

For this scenario, my recommendation would be to use Data flow which gives us the ability to perform lots of transformations which is not possible using ADF activities.

• Copy the Lookup output in JSON file and create a dataset on top of the JSON file
• Use Source transformation and select the newly created JSON file as Dataset. Make sure to Select 'JSON settings' as 'Array of documents'
• Use Flatten transformation to flatten the array
• Use Filter transformation with query: equals(status,'closed') to filter records which has status value='closed'

Here is the gif you can refer to for implementation:

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators