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

netcoder · Accepted Answer · 2010-11-21 17:49:18Z

163

Try this:

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

answered Nov 21, 2010 at 17:49

netcoder

68k19 gold badges129 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jonathan Tran Over a year ago

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

Yzmir Ramirez Over a year ago

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

user2043553 Over a year ago

@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.

Yzmir Ramirez Over a year ago

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

metatechbe Over a year ago

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

KK1402 · Accepted Answer · 2015-03-04 16:08:13Z

139

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!

answered Mar 4, 2015 at 16:08

KK1402

1,4911 gold badge9 silver badges4 bronze badges

7 Comments

Zac Over a year ago

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

Pixelmixer Over a year ago

+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.

tinker Over a year ago

I agree this should be the accepted answer on the question

Camway Over a year ago

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

markson edwardson Over a year ago

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.

|

Yuval F · Accepted Answer · 2010-11-21 19:57:36Z

71

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

answered Nov 21, 2010 at 19:57

Yuval F

20.6k5 gold badges47 silver badges70 bronze badges

1 Comment

Sudheer Aedama Over a year ago

This should be marked as the correct answer. See stackoverflow.com/questions/10722145/…

Matthias M · Accepted Answer · 2014-01-13 10:11:08Z

13

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

answered Jan 13, 2014 at 10:11

Matthias M

15.1k18 gold badges99 silver badges134 bronze badges

2 Comments

jared Over a year ago

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

Matthias M Over a year ago

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

suhyunjeon · Accepted Answer · 2015-02-09 07:49:53Z

3

You can also use it like this.

fq=!id:['' TO *]

answered Feb 9, 2015 at 7:49

suhyunjeon

1611 silver badge9 bronze badges

Comments

user8255242 · Accepted Answer · 2022-06-16 01:16:59Z

2

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.

answered Jun 16, 2022 at 1:16

user8255242

311 bronze badge

Comments

Austin Henley · Accepted Answer · 2012-09-24 04:54:29Z

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

Nimrod Cohen · Accepted Answer · 2015-02-10 15:37:37Z

1

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

answered Feb 10, 2015 at 15:37

Nimrod Cohen

112 bronze badges

Collectives™ on Stack Overflow

How to query SOLR for empty fields?

8 Answers 8

5 Comments

7 Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

5 Comments

7 Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related