Query for null?


(bailey) #1

Hi All-

What is the correct method to search for an attribute that is a null
value?
I'm attempting to search across 3 fields:
query_string : {
query : "rootId:123 AND type:discussion AND
parentId:null"
}

Everything returns as expected when I leave off "AND parentId:null".
When I add "AND parentId:null" back to the query string I get an empty
array back.

Cheers!


(Lukáš Vlček) #2

Hi,

I think it depends on how you defined mapping for null value of that field.
See mapping of core types:
http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/
http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/For
example if your parentId is of string type and you used { "null_value" :
"na" } mapping then I would try "... AND parentId:na" in your query.

Regards,
Lukas

On Tue, Jul 20, 2010 at 11:34 PM, bailey matt.bailey@gmail.com wrote:

Hi All-

What is the correct method to search for an attribute that is a null
value?
I'm attempting to search across 3 fields:
query_string : {
query : "rootId:123 AND type:discussion AND
parentId:null"
}

Everything returns as expected when I leave off "AND parentId:null".
When I add "AND parentId:null" back to the query string I get an empty
array back.

Cheers!


(Shay Banon) #3

There isn't really a way to do it in search engines simply. One way to solve
it is using the already provided ability to define null_value in the
mappings (for example, na for string, or -1 for numbers) and then search
on it. There is another way to solve this using a "field cache" approach
(similar to what is used for sorting and faceting), but its not implemented
in elasticsearch yet.

-shay.banon

On Wed, Jul 21, 2010 at 12:34 AM, bailey matt.bailey@gmail.com wrote:

Hi All-

What is the correct method to search for an attribute that is a null
value?
I'm attempting to search across 3 fields:
query_string : {
query : "rootId:123 AND type:discussion AND
parentId:null"
}

Everything returns as expected when I leave off "AND parentId:null".
When I add "AND parentId:null" back to the query string I get an empty
array back.

Cheers!


(bailey) #4

Hi Lukas-

Thanks for the reply.
I do not have any mappings set up, so the null value is what's being
stored.
Is it suggested to not store actual null values and implement mappings
instead?

Thanks.

On Jul 20, 5:45 pm, Lukáš Vlček lukas.vl...@gmail.com wrote:

Hi,

I think it depends on how you defined mapping for null value of that field.
See mapping of core types:http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/
http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/For
example if your parentId is of string type and you used { "null_value" :
"na" } mapping then I would try "... AND parentId:na" in your query.

Regards,
Lukas

On Tue, Jul 20, 2010 at 11:34 PM, bailey matt.bai...@gmail.com wrote:

Hi All-

What is the correct method to search for an attribute that is a null
value?
I'm attempting to search across 3 fields:
query_string : {
query : "rootId:123 AND type:discussion AND
parentId:null"
}

Everything returns as expected when I leave off "AND parentId:null".
When I add "AND parentId:null" back to the query string I get an empty
array back.

Cheers!


(Lukáš Vlček) #5

Hi,

On Wed, Jul 21, 2010 at 3:35 PM, bailey matt.bailey@gmail.com wrote:

Hi Lukas-

Thanks for the reply.
I do not have any mappings set up, so the null value is what's being
stored.

The default is not to store null values into index at all. So if you have no
mapping then your field probably does not make it into index.

Is it suggested to not store actual null values and implement mappings
instead?

Yes. As Shay pointed out that is the only possibility in ES right now.

Thanks.

On Jul 20, 5:45 pm, Lukáš Vlček lukas.vl...@gmail.com wrote:

Hi,

I think it depends on how you defined mapping for null value of that
field.
See mapping of core types:
http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/
<http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/
For
example if your parentId is of string type and you used { "null_value" :
"na" } mapping then I would try "... AND parentId:na" in your query.

Regards,
Lukas

On Tue, Jul 20, 2010 at 11:34 PM, bailey matt.bai...@gmail.com wrote:

Hi All-

What is the correct method to search for an attribute that is a null
value?
I'm attempting to search across 3 fields:
query_string : {
query : "rootId:123 AND type:discussion AND
parentId:null"
}

Everything returns as expected when I leave off "AND parentId:null".
When I add "AND parentId:null" back to the query string I get an empty
array back.

Cheers!

Regards,
Lukas


(bailey) #6

Thanks for the clarifications Lukas.

On Jul 21, 9:43 am, Lukáš Vlček lukas.vl...@gmail.com wrote:

Hi,

On Wed, Jul 21, 2010 at 3:35 PM, bailey matt.bai...@gmail.com wrote:

Hi Lukas-

Thanks for the reply.
I do not have any mappings set up, so the null value is what's being
stored.

The default is not to store null values into index at all. So if you have no
mapping then your field probably does not make it into index.

Is it suggested to not store actual null values and implement mappings
instead?

Yes. As Shay pointed out that is the only possibility in ES right now.

Thanks.

On Jul 20, 5:45 pm, Lukáš Vlček lukas.vl...@gmail.com wrote:

Hi,

I think it depends on how you defined mapping for null value of that
field.
See mapping of core types:
http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/
<http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/
For
example if your parentId is of string type and you used { "null_value" :
"na" } mapping then I would try "... AND parentId:na" in your query.

Regards,
Lukas

On Tue, Jul 20, 2010 at 11:34 PM, bailey matt.bai...@gmail.com wrote:

Hi All-

What is the correct method to search for an attribute that is a null
value?
I'm attempting to search across 3 fields:
query_string : {
query : "rootId:123 AND type:discussion AND
parentId:null"
}

Everything returns as expected when I leave off "AND parentId:null".
When I add "AND parentId:null" back to the query string I get an empty
array back.

Cheers!

Regards,
Lukas


(ppearcy) #7

FYI, I raised a feature request issue for this:

However, afterwards, I discovered this syntax:
(-companynames:[* TO *])

This allows this query and I believe is much cleaner than default
null_values.

Thanks,
Paul

This will return me any empty or Null company names.

On Jul 21, 7:54 am, bailey matt.bai...@gmail.com wrote:

Thanks for the clarifications Lukas.

On Jul 21, 9:43 am, Lukáš Vlček lukas.vl...@gmail.com wrote:

Hi,

On Wed, Jul 21, 2010 at 3:35 PM, bailey matt.bai...@gmail.com wrote:

Hi Lukas-

Thanks for the reply.
I do not have any mappings set up, so thenullvalue is what's being
stored.

The default is not to storenullvalues into index at all. So if you have no
mapping then your field probably does not make it into index.

Is it suggested to not store actualnullvalues and implement mappings
instead?

Yes. As Shay pointed out that is the only possibility in ES right now.

Thanks.

On Jul 20, 5:45 pm, Lukáš Vlček lukas.vl...@gmail.com wrote:

Hi,

I think it depends on how you defined mapping fornullvalue of that
field.
See mapping of core types:
http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/
<http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/
For
example if your parentId is of string type and you used { "null_value" :
"na" } mapping then I would try "... AND parentId:na" in yourquery.

Regards,
Lukas

On Tue, Jul 20, 2010 at 11:34 PM, bailey matt.bai...@gmail.com wrote:

Hi All-

What is the correct method to search for an attribute that is anull
value?
I'm attempting to search across 3 fields:
query_string : {
query: "rootId:123 AND type:discussion AND
parentId:null"
}

Everything returns as expected when I leave off "AND parentId:null".
When I add "AND parentId:null" back to thequerystring I get an empty
array back.

Cheers!

Regards,
Lukas


(Shay Banon) #8

Hi,

Yea, the below trick would work, but not optimally. I had an idea of
having optimized field cache like existence markers for fields, which should
work better (at the expense of more memory).

-shay.banon

On Sat, Jul 31, 2010 at 1:58 AM, Paul ppearcy@gmail.com wrote:

FYI, I raised a feature request issue for this:
http://github.com/elasticsearch/elasticsearch/issues/#issue/281

However, afterwards, I discovered this syntax:
(-companynames:[* TO *])

This allows this query and I believe is much cleaner than default
null_values.

Thanks,
Paul

This will return me any empty or Null company names.

On Jul 21, 7:54 am, bailey matt.bai...@gmail.com wrote:

Thanks for the clarifications Lukas.

On Jul 21, 9:43 am, Lukáš Vlček lukas.vl...@gmail.com wrote:

Hi,

On Wed, Jul 21, 2010 at 3:35 PM, bailey matt.bai...@gmail.com wrote:

Hi Lukas-

Thanks for the reply.
I do not have any mappings set up, so thenullvalue is what's being
stored.

The default is not to storenullvalues into index at all. So if you have
no

mapping then your field probably does not make it into index.

Is it suggested to not store actualnullvalues and implement mappings
instead?

Yes. As Shay pointed out that is the only possibility in ES right now.

Thanks.

On Jul 20, 5:45 pm, Lukáš Vlček lukas.vl...@gmail.com wrote:

Hi,

I think it depends on how you defined mapping fornullvalue of that
field.
See mapping of core types:
http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/
<
http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/

For
example if your parentId is of string type and you used {
"null_value" :

"na" } mapping then I would try "... AND parentId:na" in yourquery.

Regards,
Lukas

On Tue, Jul 20, 2010 at 11:34 PM, bailey matt.bai...@gmail.com
wrote:

Hi All-

What is the correct method to search for an attribute that is
anull

value?
I'm attempting to search across 3 fields:
query_string : {
query: "rootId:123 AND type:discussion AND
parentId:null"
}

Everything returns as expected when I leave off "AND
parentId:null".

When I add "AND parentId:null" back to thequerystring I get an
empty

array back.

Cheers!

Regards,
Lukas


(elasticsearcher) #9

However, afterwards, I discovered this syntax:
(-companynames:[* TO *])

Hi,

Sorry to revive a dead thread but could you explain what you mean by this syntax? I tried using it as a query string and then I thought maybe a range query but neither of them retrieved the results I expected.

Thanks for your help.


(ppearcy) #10

Hey,
No prob reviving the dead from my perspective :slight_smile:

field:[* TO *]

Should return all the docs that have "field" populated. Negating it
should yield the negative set:
-companynames:[* TO *]

In pure Lucene purely negative queries don't work as the negative set
must be removed by something. So in Lucene syntax, it'd look like:
(-field:[* TO *] AND :)

Kimchy was clever enough to detect pure negatives in ES and do this
implicitly in some cases. Here are more details on when it isn't
automatically handled:

Regards,
Paul

On Oct 21, 9:32 am, elasticsearcher elasticsearc...@gmail.com wrote:

ppearcy wrote:

However, afterwards, I discovered this syntax:
(-companynames:[* TO *])

Hi,

Sorry to revive a dead thread but could you explain what you mean by this
syntax? I tried using it as a query string and then I thought maybe a range
query but neither of them retrieved the results I expected.

Thanks for your help.

View this message in context:http://elasticsearch-users.115913.n3.nabble.com/Query-for-null-tp9826...
Sent from the ElasticSearch Users mailing list archive at Nabble.com.


(Shay Banon) #11

Hey, check the latest "Just Pushed" mail to the mailing list, I added this
feature to be built into elasticsearch.

On Fri, Oct 22, 2010 at 2:30 AM, Paul ppearcy@gmail.com wrote:

Hey,
No prob reviving the dead from my perspective :slight_smile:

field:[* TO *]

Should return all the docs that have "field" populated. Negating it
should yield the negative set:
-companynames:[* TO *]

In pure Lucene purely negative queries don't work as the negative set
must be removed by something. So in Lucene syntax, it'd look like:
(-field:[* TO *] AND :)

Kimchy was clever enough to detect pure negatives in ES and do this
implicitly in some cases. Here are more details on when it isn't
automatically handled:
http://github.com/elasticsearch/elasticsearch/issues#issue/320

Regards,
Paul

On Oct 21, 9:32 am, elasticsearcher elasticsearc...@gmail.com wrote:

ppearcy wrote:

However, afterwards, I discovered this syntax:
(-companynames:[* TO *])

Hi,

Sorry to revive a dead thread but could you explain what you mean by this
syntax? I tried using it as a query string and then I thought maybe a
range
query but neither of them retrieved the results I expected.

Thanks for your help.

View this message in context:
http://elasticsearch-users.115913.n3.nabble.com/Query-for-null-tp9826...
Sent from the ElasticSearch Users mailing list archive at Nabble.com.


(elasticsearcher) #12

Awesome, thanks!


(system) #13