Exclude empty-string, null and non-existant

(Pictor) #1

How does one query for all documents having a field with non empty value? (aka mandatory value)

I can exclude the null values or non existing fields via the exists (negated) query, but I also need to ensure that the values is not an empty string.
From what I know an empty string is a non-null value, so it will be included in results from exists query.

What's the best (smartest & most performant) way to do this filtering?

  • could use a script to check for the property field.length > 0, but I don't like to rely on scripting languages in my queries (if not strictly needed); also I suppose is not ideal for performances.
  • could use a "wildcard": { "fieldname": "?*" } together with the must exists query. It's the solution I am using at the moment but, still, I believe there must be some better performing way than using regular expressions.
  • could maybe (?) use some parameter for the exists query so that also empty strings are treated as null/non-existing value.

What I want to achieve is to treat empty-string, null and non-existing as the same (kind of "invalid value").

Also, an additional/optional curiosity is if it makes sense (and how) to do a trim of the field value, to exclude strings like " ".

I was using the missing query for the opposite case (empty field).
Is it completely deprecated? Should I always use the must_not-exists query instead?

(Pictor) #2

Looks like using "wildcard": { "fieldname": "?*" } is the best solution at the moment.
But I am not sure how it scales when the wildcard+exists check is performed on more than one field.
Not sure how much could it slow down the search.

Please, could anybody with solid experience enlighten me?