Filter Lookup Results from values in Second Lookup in Azure Data Factory

user_7596217 0
Viewed 3318

I have two lookups within an Until activity in ADF. The first lookup (BookList) is a list of books that look like the JSON listed below.

[
  {
    "BookID": 1,
    "BookName": "Book A"
  },
  {
    "BookID": 2,
    "BookName": "Book B"
  }
]

The second lookup is a list of books that I want to exclude from the first list (ExcludedBooks) which is listed below.

[
  {
    "BookID": 2,
    "BookName": "Book B"
  }
]

After these two lookups, I have a Filter activity whose items are the values from BookList lookup. I would like the filter condition to be based on the BookID value not being listed in the ExcludedBooks values, but I'm not sure how to write this condition based on the collection functions in ADF. What I have is listed below which does not work.

@not(contains(activity('ExcludedBooks').output.value, item().BookID))

I realize one way to solve this is to loop through each record of the ExcludedBooks and use a SetVariable activity to build an array of BookIDs which WOULD work with the collection function Contains(), but ADF does not allow nested activity groups for some reason (ForEach within an Until).

I also cannot set the list of excluded books outside of the Until activtity as it will change with each iteration of the Until activity. I also realize the workaround to the nested group activity restriction is to create a completely different pipeline, but that is not ideal and creates unnecessary complexity when trying to return the results.

Does anyone have any suggestions for how to filter the results of a lookup based on the results of another lookup?

azure-data-factory
1 Answers

Below expression doesn't work because item of activity('ExcludedBooks').output.value is object,item().BookID is number.

@not(contains(activity('ExcludedBooks').output.value, item().BookID))

If your each item in ExcludedBooks is the same as your item in BookList(like your provide sample),you can use this expression:@not(contains(activity('ExcludedBooks').output.value, item())).

My test result:

enter image description here

For another hand,if your item in ExcludedBooks like this json(BookList is the same as your provided):

[
  {
    "BookID": 2,
    "BookName": "Book B",
    "num": 22
  }
]

you can only compare their BookID by using this expression: @not(contains(join(activity('ExcludedBooks').output.value,','),concat('"BookID":',item().BookID,',')))

(cast activity('ExcludedBooks').output.value to string,concat item() in 'BookList' as "BookID":2, and check whether 'ExcludedBooks' string contains 'BookList' item string)

My test result: enter image description here

Hope this can help you.

user_133676380
Related