Search code examples
solr

Using not equal or missing in SOLR query

Looking for a way to convert following expression to solr query.

ISNULL(field) OR field != 1

I have tried several things,

-field:1 OR -field:*

-(-field:1 AND field:*)

(-field:1 OR (*:* -field:*))

Solution

  • Solr Standard Query Parser does not support searching a field for empty/null value. In this situation the filter needs to be negated (exluding documents having any value in the field) so that the query remains valid.

    However, using a range query is preferred to prevent inconsitent behaviors when using wildcard within negative subqueries.

    So ISNULL(field) becomes -field:[* TO *]

    Also, when applying a filter and a negated filter in the same fq param, you need to add *:* on the latter and ensure the first - operator applies to only the first filter using parentheses, otherwise the OR won't apply properly, . So if this filter is to be combined with others, ensure the filter intersects the resultset (assuming that filter should apply to all documents) :

    fq=(-field:[* TO *]) OR (*:* AND -field:<val>)

    Note this is equivalent to :

    fq=-(field:[* TO *] AND field:<val>)

    Which means you only need that part (as long as <val> is not null, ie. matches with a value different than 1 includes null/empty values) :

    fq=-field:<val>