Query for null?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Query for null?

bailey
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!
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query for null?

Lukáš Vlček
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/
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 <[hidden email]> 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!

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query for null?

kimchy
Administrator
In reply to this post by bailey
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 <[hidden email]> 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!

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query for null?

bailey
In reply to this post by Lukáš Vlček
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 <[hidden email]> 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 <[hidden email]> 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!
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query for null?

Lukáš Vlček
Hi,

On Wed, Jul 21, 2010 at 3:35 PM, bailey <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query for null?

bailey
Thanks for the clarifications Lukas.

On Jul 21, 9:43 am, Lukáš Vlček <[hidden email]> wrote:

> Hi,
>
> On Wed, Jul 21, 2010 at 3:35 PM, bailey <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query for null?

ppearcy
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 <[hidden email]> wrote:

> Thanks for the clarifications Lukas.
>
> On Jul 21, 9:43 am, Lukáš Vlček <[hidden email]> wrote:
>
>
>
> > Hi,
>
> > On Wed, Jul 21, 2010 at 3:35 PM, bailey <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query for null?

kimchy
Administrator
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 <[hidden email]> 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 <[hidden email]> wrote:
> Thanks for the clarifications Lukas.
>
> On Jul 21, 9:43 am, Lukáš Vlček <[hidden email]> wrote:
>
>
>
> > Hi,
>
> > On Wed, Jul 21, 2010 at 3:35 PM, bailey <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query for null?

elasticsearcher
In reply to this post by ppearcy
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.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query for null?

ppearcy
Hey,
  No prob reviving the dead from my perspective :-)

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 <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query for null?

kimchy
Administrator
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 <[hidden email]> wrote:
Hey,
 No prob reviving the dead from my perspective :-)

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 <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Query for null?

elasticsearcher
Awesome, thanks!
Loading...