How to compare multivalue fields? Union, intersection, membership tests

[emottola]

When comparing multivalue fields, there are a number of relationships one might be interested in.

Equality is easy to check, but what about more complex relationships? 
Are any members of f1 in f2?
What fields do f1 and f2 have in common(intersection)?
What fields are unique to f1?

[bowesmana]

mvmap is your friend here. See this example which creates 2 random MV fields and then does the comparisons

| makeresults
| fields - _time
| eval f1=mvrange(0,10,(random() % 3 + 1))
| eval f2=mvrange(5,20,(random() % 4 + 1))
| eval f1_exists_in_f2=max(f1_exists_in_f2, mvmap(f1, if(isnotnull(mvfind(f2, f1)), 1, 0)))
| eval f1_values_in_f2=mvmap(f1, if(f1=f2, f1, null()))
| eval f1_values_not_in_f2=mvmap(f1, if(f1!=f2, f1, null()))
| table f1 f2 f1_exists* f1_values*

[emottola · Accepted solution]

To answer this, I've prepared a makeresults with examples of many of these mvfield comparisons.
Hopefully someone finds these useful!

If you came searching for a different usecase, plase leave a comment with the keywords that would have helped you find this post so others will have an easier time

| makeresults 
| eval f1="a", f2=split("a,b,c,d", ",") 
| append 
    [| makeresults 
    | eval f1="a", f2=split("b,c,d",",")] 
| append 
    [| makeresults 
    | eval f1=split("a,b", ","), f2=split("a,b",",")] 
| append 
    [| makeresults 
    | eval f1=split("b,a", ","), f2=split("a,b",",")] 
| append 
    [| makeresults 
    | eval f1=split("a,b", ","), f2=split("a,b,c,d",",")] 
| append 
    [| makeresults 
    | eval f1=split("c,a", ","), f2=split("a,b,c,d",",")] 
| append 
    [| makeresults 
    | eval f1=split("a,b", ","), f2=split("b,c,d",",")] 
| append 
    [| makeresults 
    | eval f1=split("a,b", ","), f2=split("c,d",",")] 
| fields - _time

| eval intersection=mvmap(f1, if(f1=f2, f1, null()))
| eval f1_not_f2=mvmap(f1, if(f1==f2, null(), f1))
| eval f2_not_f1=mvmap(f2, if(f2==f1, null(), f2))
| eval union = mvsort(mvdedup(mvappend(f1, f2)))

| eval equality = if(f1=f2, 1, 0)
| eval equivalence = if(mvmap(f1, if(f1==f2, 0, f1)) == mvmap(f2, if(f2==f1, 0, f2)), 1, 0)
| eval any_f1_in_f2 = if(mvcount(intersection)>0, 1, 0)
| eval all_f1_in_f2 = if(mvmap(f1, if(f1==intersection, 0, f1)) == mvmap(intersection, if(intersection==f1, 0, intersection)), 1, 0)

| table f1, f2, f1_not_f2, intersection, f2_not_f1, union, equality, equivalence, all_f1_in_f2, any_f1_in_f2