How to subtract arrays in DQL?

[bretonne]

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.

[krzysztof_hoja · Accepted solution]

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[])))