How to subtract arrays in DQL?

I want to find out the diff between two arrays and list them out with DQL.

Something like | fields array1, array2, diff

Diff needs to be calculated but not sure how to do that.

Here I have

array 1 = [“apple”, “orange”, “lychee”, “grapes”, “pineapple”]

array 2 = [“apple”, “orange”, “lychee”]

Then I would like diff result to be [“grapes”, “pineapple”].

How can I get diff through DQL?

thanks.

Yes, it is possible with iterative expressions and arrayIndexOf function. You need to check for each element of 1st array if it does not exist in 2nd array.

data record(a1=array( "apple", "orange", "lychee", "grapes", "pineapple" ), a2=array("apple", "orange", "lychee") )
| fieldsAdd diff = arrayRemoveNulls(iCollectArray(if ( arrayIndexOf( a2, a1[] ) < 0, a1[])))