116

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

Eric Wilson
  • 51,818
  • 71
  • 192
  • 262

7 Answers7

150

Try this:

?q=-id:["" TO *]
netcoder
  • 61,842
  • 17
  • 117
  • 139
  • 7
    Even though the SolrQuerySyntax page says -id:[* TO *], only -id:["" TO *] worked for me on solr 1.4. – Jonathan Tran Dec 14 '10 at 05:35
  • 1
    @user2043553 Nope, if you `?q=-id:*` you get `Cannot parse '-q:*': '*' or '?' not allowed as first character in WildcardQuery` – Yzmir Ramirez Dec 05 '14 at 18:47
  • 1
    @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](http://stackoverflow.com/questions/15979/wildcardquery-error-in-solr). – user2043553 Dec 08 '14 at 11:28
  • Sorry, forgot the version ... `Lucene Specification Version: 3.2.0` I was using. Glad they added the syntax in Solr 4.5.1. – Yzmir Ramirez Dec 08 '14 at 19:00
  • Beware that this syntax seems to also return rows whose field value starts with a whitespace (in Solr 4.3) – metatechbe Mar 04 '15 at 10:29
  • why didnt they created simple query syntax like MySQL so everyone already knows about that – Umair Ayub Dec 20 '18 at 10:17
  • why does this work? I mean we are including "" in expression but it excludes field values with empty space i.e. "" – genonymous Apr 02 '21 at 21:57
  • SOLR WTF. why not just `id:""` ... – Umair Ayub May 26 '21 at 12:24
101

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!

KK1402
  • 1,111
  • 1
  • 7
  • 4
  • 5
    This answer deserves more points than it actually has. You saved us a lot of time! – Zac Jun 10 '15 at 13:37
  • 1
    +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. – Pixelmixer May 10 '16 at 15:17
  • 2
    I agree this should be the accepted answer on the question – tinker Nov 06 '16 at 15:42
  • 2
    You saved me so much of a headache. I'm not sure thank you is sufficient. – Camway Aug 03 '18 at 20:37
70

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

Yuval F
  • 20,437
  • 4
  • 41
  • 67
  • 1
    This should be marked as the correct answer. See http://stackoverflow.com/questions/10722145/solr-how-do-i-construct-a-query-that-requires-a-not-null-location-field – Sudheer Aedama Aug 17 '15 at 17:54
12

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

Matthias M
  • 8,008
  • 11
  • 67
  • 83
2

You can also use it like this. 

fq=!id:['' TO *]
user1976546
  • 121
  • 1
  • 8
1

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));
Austin Henley
  • 4,566
  • 13
  • 42
  • 74
Adriano Galesso Alves
  • 740
  • 14
  • 21
1

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

Nimrod Cohen
  • 11
  • 2