How to query SOLR for empty fields?

118
votes

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

7
solrlucene

7 Answers

151
votes

Try this:

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

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!

70
votes

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

12
votes

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 *]

2
votes

You can also use it like this. 

fq=!id:['' TO *]
1
votes

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));
1
votes

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