37

I have a Solr index with a set of coordinates stored as a Location type; I would like to query for documents where this field has a non-null value.

What is the query syntax to perform the NOT NULL check on a Location field?

STW
  • 44,917
  • 17
  • 105
  • 161

4 Answers4

67

The canonical way is this:

fieldName:[* TO *]

Using a '' on the left side as Paige Cook suggested will probably work too but I don't trust it as much as I do the above. And since this is to a Location field, you will probably have to do this against one of the two underlying actual fields versus this logical composite field. They start with fieldName and end with some sort of numeric suffix; look in the Schema Browser to see what the actual name is.

An important thing to be aware of here is that a query like this is expensive for Solr to do as it much do a full index scan on this field. If you have many distinct location field values (thousands on up?), then this is a big deal. If you do this in a filter query, then it will be cached and it's perhaps moot. If you want this query to run fast, then at index time you should index a boolean field to indicate if there is a value in this field or not.

Scarabee
  • 5,437
  • 5
  • 29
  • 55
David Smiley
  • 4,102
  • 2
  • 19
  • 18
  • Thanks David, the [* TO *] syntax, combined with checking both backing fields, appears to work correctly (e.g. `field_0_coord:[* TO *] AND field_1_coord:[* TO *]`). Thanks for the performance tip as well! – STW May 23 '12 at 19:34
  • 1
    You don't have to do this against both coord fields, which is twice as slow, just one. If one has a value, the other does, and vice versa. – David Smiley May 25 '12 at 04:00
  • Because I just came across this "solution" in our project: Don't use this for a String field. – SoS Sep 28 '20 at 13:04
  • @DavidSmiley I can also use fieldName:(*) to achieve NOT NULL values for field. Can you please say if is there any performance impact difference between these 2 queries [* TO *] and (*) – Chirag Shah Jan 09 '23 at 10:11
  • @ChiragShah your syntax confused the StackOverflow formatting but I assume you are asking if `field:[* TO *]` is equivalent to `field:*`. I think these are the same as of Jan 2020. – David Smiley Jan 25 '23 at 20:44
12

You can add this to your query fieldname:['' TO *]. This will be the equivalent of a NOT NULL check.

I got this from the post - Solr - Field Not Null Searches

Paige Cook
  • 22,415
  • 3
  • 57
  • 68
  • Thanks Paige, but unfortunately that doesn't appear to work for the `Location` – STW May 23 '12 at 18:53
  • to expand a bit more, the field as well as the backing fields are numeric; evaluating them against `''` results in a NumberFormatException – STW May 23 '12 at 19:35
  • Sorry that I missed that this was numeric. David Smiley has the correct answer. – Paige Cook May 24 '12 at 01:36
7

For a location field (solr.LatLonType), use the following filter query: -fieldName:[-90,-180 TO 90,180] (the coordinates outside this range are still invalid)

mirelon
  • 4,896
  • 6
  • 40
  • 70
-6

Try this q= !fieldname:NULL this will be equivalent to Fieldvalue is NOT NULL.

Asked by STW I have a Solr index with a set of coordinates stored as a Location type; I would like to query for documents where this field has a non-null value.

What is the query syntax to perform the NOT NULL check on a Location field?

HDJEMAI
  • 9,436
  • 46
  • 67
  • 93
Anuradha
  • 11
  • The query `!fieldname:NULL` isn't working, shows the error 400: java.text.ParseException: missing parens: NULL – Murz May 12 '21 at 07:04