Nested OR conditions in azure synapse

Question

I have a filter activity that filters out files base on specific values. currently I am using @contains(item().name,'KU001') to check if 'KU001' is present in the file Name. But I want to check multiple conditions in OR so that if any of the matching values are found it filters out.

So in addition to it I also need to check 'KU002' , 'KU001','KU004' are present in file names. Since '@contains' cannot be nested, and we cannot add multiple 'OR' conditions what could be the best possible approach.

Answer 1

If your KU___ items are more, then you can try the below approach.

First create an array of your KU___ items like below.

@createArray('KU001','KU002','KU003','KU004')

Create these variable in the pipeline.

Give this array to a ForEach activity and check the Sequential box in it.

Inside ForEach, use the set variable activities and filter activities combination like below.

When using filter activity inside a for loop, @item() value will be taken from filter activity. So, to compare the item from for each activity, first store the current @item() of for-loop in a variable.

Then use filter activity. Give your names array(values to filter) to this and in condition, use this expression.

@contains(item().name,variables('cur_item'))

This will filter out the matching records in every iteration, but to avoid the duplication of matching records, use union of the res(empty array in first iteration) and filter output array and store it in a temp_arr array with below expression.

@union(variables('res'),activity('Filter1').output.Value)

To do this in every iteration, reassign the temp_arr array to res array.

After ForEach, the required filtered values will be stored in the res variable like below.

If your KU___ items are less, then you can directly use @or() function in the filter activity instead of the for-loop approach like below.

@or(or(contains(item().name,'KU001'),contains(item().name,'KU002')),or(contains(item().name,'KU003'),contains(item().name,'KU004')))

You can use any of the above approach as per your requirement.