Replicating SQL IN operator in Azure Data Factory

(2020-May-01) There is a current limitation in Azure Data Factory (ADF) to accept only two conditions for the OR function.

You won't be able to specify the following expression to evaluate 3 possible values for a variable:
or(equals(variables('var1'), 'A'), equals(variables('var1'), 'B'), equals(variables('var1'), 'C')) - not possible.

However only this use-case for the OR function with 2 condition could be possible:
or(equals(variables('var1'), 'A'), equals(variables('var1'), 'B')) - limit of two conditions

But what if we have an ability to check if a particular element variable/parameter/other ADF object value belongs to a range of values (array of value), similarly to what we can do with the IN operator in SQL language, this would definitely solve our problem and remove the limitation of logical conditions to check.

The previous attempted OR expression with 3 conditions can be successfully written this way:
contains(createArray('A', 'B', 'C'),variables('var1'))

An array of values in your real-life ADF pipelines could come as an output of other previous activities or it can be a hardcoded list of values that you would want your variables to evaluate.

A similar SQL IN construction:

Could be created in Azure Data Factory this way:

Simple ADF tip to share! :-)


  1. Thanks for the info.
    How can we filter out couple of values from the GetMetadata json output before sending it to the ForEachLoop

    1. If this is just an array element, ten you can also use Filter activity task for this. Or you can always do more transformation if save the output of the GetMetadata task and make further modifications before passing this variable to your For Each loop container.

  2. Thanks for the answer , it worked using contains expression to filter multiple values


Post a Comment