How to query SOLR for empty fields?

Question

I have a large solr index, and I have noticed some fields are not updated correctly (the index is dynamic).

This has resulted in some fields having an empty "id" field.

I have tried these queries, but they didn't work:

 id:''
 id:NULL
 id:null
 id:""
 id:
 id:['' TO *]

Is there a way to query empty fields?

Thanks

Caffrey

Answer 1

Try this:

?q=-id:["" TO *]

Passional

Even though the SolrQuerySyntax page says -id:[* TO *], only -id:["" TO *] worked for me on solr 1.4. – Propman

@user2043553 Nope, if you ?q=-id:* you get Cannot parse '-q:*': '*' or '?' not allowed as first character in WildcardQuery – Outface

@YzmirRamirez I've tried with the example of Solr 4.5.1 and ?q=-id:* seems to work as expected. Maybe the parsing error is related to this issue. – Katharyn

Sorry, forgot the version ... Lucene Specification Version: 3.2.0 I was using. Glad they added the syntax in Solr 4.5.1. – Outface

Beware that this syntax seems to also return rows whose field value starts with a whitespace (in Solr 4.3) – Catamenia

Answer 2

One caveat! If you want to compose this via OR or AND you cannot use it in this form:

-myfield:*

but you must use

(*:* NOT myfield:*)

This form is perfectly composable. Apparently SOLR will expand the first form to the second, but only when it is a top node. Hope this saves you some time!

Dempstor

This answer deserves more points than it actually has. You saved us a lot of time! – Idiotism

+1 here as well. I implemented the other options but I had to include it in an fq= rather than q= and also had to implement an OR to check if the field was empty OR had a specific value. This is the only option that worked for that use case. – Eustoliaeutectic

I agree this should be the accepted answer on the question – Lymphosarcoma

You saved me so much of a headache. I'm not sure thank you is sufficient. – Conflict

I also had to use fq, with q = *:*. Also note you obviously can't do this check against non-indexed/only-stored fields which caught me out for a second. – Irresolution

Is NOT myfield:* completely equivalent to myfield:(NOT *)? And could we use the latter in composition, like (*:* AND myfield:(NOT *))? Thanks for your help everyone! – Tapdance

What do you mean a "top node"? – Incessant

Answer 3

According to SolrQuerySyntax, you can use q=-id:[* TO *].

Lust

This should be marked as the correct answer. See #10722645 – Irvinirvine

Answer 4

If you have a large index, you should use a default value

   <field ... default="EMPTY" />

and then query for this default value. This is much more efficient than q=-id:["" TO *]

Gardenia

Would this only work for fields of type String? How would you do it for for boolean? – Eanes

I guess, it should work in the same way. But I have never checked it. – Gardenia

Answer 5

You can also use it like this.

fq=!id:['' TO *]

Pronto

Answer 6

If you are using SolrSharp, it does not support negative queries.

You need to change QueryParameter.cs (Create a new parameter)

private bool _negativeQuery = false;

public QueryParameter(string field, string value, ParameterJoin parameterJoin = ParameterJoin.AND, bool negativeQuery = false)
{
    this._field = field;
    this._value = value.Trim();
    this._parameterJoin = parameterJoin;
    this._negativeQuery = negativeQuery;
}

public bool NegativeQuery
{
    get { return _negativeQuery; }
    set { _negativeQuery = value; }
}

And in QueryParameterCollection.cs class, the ToString() override, looks if the Negative parameter is true

arQ[x] = (qp.NegativeQuery ? "-(" : "(") + qp.ToString() + ")" + (qp.Boost != 1 ? "^" + qp.Boost.ToString() : "");

When you call the parameter creator, if it's a negative value. Simple change the propertie

List<QueryParameter> QueryParameters = new List<QueryParameter>();
QueryParameters.Add(new QueryParameter("PartnerList", "[* TO *]", ParameterJoin.AND, true));

Chinkiang

Answer 7

you can do it with filter query q=*:*&fq=-id:*

Jellaba

Answer 8

A note added here, to make the field searchable first, it needs the field type in SOLR schema set to "indexed = true". Then you can use "field_name:*" for string type and "field_name:[* TO *]" for numeric type.

Visayan