How to make a difference of two arrays in ClickHouse?

One of the great things about ClickHouse is its support for arrays, which can be used to store and manipulate large sets of data.

The process of making a difference of two arrays in ClickHouse is relatively straightforward. The basic idea is to use the arrayFilter function to filter out any elements that are present in both arrays. To do this, we will use a simple query that looks like this:

select arrayFilter(x -> has(firstArray, x) != true, secondArray) diff;

This query takes two arrays, firstArray and secondArray, and filters out any elements that are present in both arrays. The result of this query is a new array, diff, which contains only the elements that are unique to the secondArray.

The key to this query is the arrayFilter function, which takes two arguments: a lambda function, and an array. The lambda function is used to filter out elements that are not unique to the secondArray, while the array is the one we want to filter. The has function used in the lambda function is a built-in ClickHouse function that returns true if the element passed to it is present in the specified array.