Solr - How do I construct a query that requires a NOT NULL Location field

Question

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?

Bagley

Answer 1

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.

Sublunar

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! – Bagley

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. – Sublunar

Because I just came across this "solution" in our project: Don't use this for a String field. – Dredger

@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 () – Chef

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

Answer 2

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

Titanothere

Thanks Paige, but unfortunately that doesn't appear to work for the Location – Bagley

to expand a bit more, the field as well as the backing fields are numeric; evaluating them against '' results in a NumberFormatException – Bagley

Sorry that I missed that this was numeric. David Smiley has the correct answer. – Titanothere

Answer 3

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)

Triangle

Answer 4

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?

Harmonious

The query !fieldname:NULL isn't working, shows the error 400: java.text.ParseException: missing parens: NULL – Sift