Using Bucket Search to find empty field values

The bucket search interface is an under used feature in Content Editor, at least underutilized by me.  One of the things that's always thrown me off is the OR vs. AND syntax.  I'm currently doing some discovery using the Helix.Examples site, and I need to clear the related product values.  I thought it would be helpful to be able to see the ones I've already cleared, or the ones that I still have left to do.  (There's just 20, or else I would have installed Sitecore PowerShell Extensions to do this.)

So far, I've cleared the Related Products field on 5 of the 20 Product Detail Page items.  Let's see this in the bucket query:

Okay, let's restrict to my bucketed type, to avoid the Shared Content and the Bucket folders (like "2019"). Type "tem" and tab out the autocomplete to "template"

A weird bit of UI glitchiness is you will just see "Template" (at least it's capitalized now), until you type a colon (:), then you get a little selector field with autocomplete. Select your template type:

 And we get 20 results as expected.

Okay, now let's add the filter by field value. Type a "c" and tab out to "custom", then type a colon to get the field selector:

Now type "r" and select "related products" which brings up this:

Type an asterisk after the pipe symbol to select any non-empty value:

Why did we get 20 results if 5 items have been cleared? The field is not empty condition is getting treated as an "OR" condition, so the template condition is causing the undesired fields values to be included.  By clicking on the magnifying glass icon next to "Related Products", we can make that an AND condition, and now we get 15. Nice!

Click it again to make it a NOT condition, and now we have only those items with that field empty, and now we get 5.

And you can see in your Search log what is going to Solr. OR, AND and NOT syntax bits called out below:

That was a lot of screen shots for a pretty simple feature, but I've always found the autocomplete a little perplexing, especially the bit about having to type a colon to make the field selector logic appear, and needed to click the condition into AND mode.

Happy Sitecoring!