topic How to subtract arrays in DQL? in DQL

How to subtract arrays in DQL?

bretonne — Tue, 25 Feb 2025 16:45:58 GMT

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.

Re: How to subtract arrays in DQL?

krzysztof_hoja — Fri, 28 Feb 2025 08:27:59 GMT

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