Using where to compare for one value in a multivalue field

[lmonahan]

Is it valid to use a where clause to compare a string value to a multivalue field in order to know if that value is one of the values in the multivalue field? 

For example,  my query returns this result where firstName is a multivalued field:

lastName    |  firstName
--------      -----------
Smith       |  Amy, Barbara, Carol
Wilson      |  Carol, Deanna, Emily

In my query I add the following to the end of my query to find all rows containing "Carol" in the multivalue field.

where firstName="Carol"

The where clause seems to work fine and returns all the row containing "Carol" in the multivalue field.  I'm wondering if its a supported syntax because I didn't find an example that looks like this and the various "mv" functions seemed to be for more complicated operations.

In this example, I'm looking to get all last names and any associated first name and then use a where clause to return anyone with a particular first name.

[bowesmana · Accepted solution]

That's a valid syntax, although you are right, I've never seen it documented that it would work like that.

The alternative MV way to do it is with mvfind, like this

| makeresults
| eval _raw="lastName    |  firstName
--------      -----------
Smith       |  Amy, Barbara, Carol
Wilson      |  Carol, Deanna, Emily"
| multikv forceheader=1
| eval firstName=trim(split(firstName, ","))
| table lastName firstName
| where !isnull(mvfind(firstName, "Carol"))