Filter using string list in ADF filter block

Question

Hello,

I am trying to load a bunch of tables from database to our datalake. I need to put in a filter in between to load only certain tables at times. The below works now

Using Filter. Items as @activity('lookuptablelist').output.value

To filter out one table:

Condition --> @equals(toLower(item().tableName),'tableone')

For more than one table:

@OR(

equals(toLower(item().tableName),'tableone'), 

equals(toLower(item().tableName),'tabletwo')

)

However, I am trying to parameterize the list of tables in the pipeline . If the parameter is null, it should consider all tables.

I tried something like this. No errors, but the filter is not accomplished. It works as expected(get all tables) when tab_filter is null

pipeline param --> table_filter=table_one,table_two

@if(equals(pipeline().parameters.table_filter,null ), equals(toLower(item().tabname),toLower(item().tabname)) , contains(toLower(item().tabname),split(pipeline().parameters.table_filter,',')))

How can I filter the output from a previous lookup activity that outputs json with a array of strings I pass as parameter input? Any help regarding this is appreciated.

Answer 1

Hi @Mahesh Kumar Arumuga Perumal

You're looking to filter a list of tables (from a Lookup activity) using a parameterized list (table_filter), with a fallback that loads all tables if the parameter is left null. That’s a great use case and can definitely be done in ADF the only issue was with how contains() was being used.

cause

In your expression, you used contains() to check if a table name exists in a list generated by split(). While this seems logical, contains() in ADF is designed to work with strings not arrays so it doesn't behave as expected when used with split(...).

To check if an item exists in an array, you should use the indexOf() function instead. Here's the corrected expression you can use in your Filter activity:

@if(
    equals(pipeline().parameters.table_filter, null),
    true,
    not(equals(indexOf(split(toLower(pipeline().parameters.table_filter), ','), toLower(item().tabname)), -1))
)

How the Expression Works:

The expression first checks whether the table_filter parameter is null. If it is, the condition returns true, allowing all tables from the Lookup activity to pass through without filtering. If table_filter contains specific values like "orders,customers", it splits that string into an array, converts both the array elements and the current item().tabname to lowercase for case-insensitive comparison, and then uses the indexOf() function to determine if the current table name exists in the array. Only tables that are included in the list will pass through the filter.

If you set:

"table_filter": "orders,products"

Then only orders and products will be processed.

If table_filter is left empty or null, all tables from the Lookup will pass through.

References:

Filter activity in ADF

Expression functions

Kindly consider upvoting this response if you found it helpful it may help other community members facing similar issues.