# Query for null? **URL:** \ **Category:** Elasticsearch\ **Created:** [July 20, 2010, 9:34pm UTC](https://discuss.elastic.co/t/query-for-null/3129 "2010-07-20T21:34:26Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![bailey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bailey/32/3278_2.png) [@bailey](https://discuss.elastic.co/u/bailey)\ **Post date:** [July 20, 2010, 9:34pm UTC](https://discuss.elastic.co/t/query-for-null/3129/1 "2010-07-20T21:34:26Z")
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! ---
**Author:** ![Lukas\_Vlcek1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas_vlcek1/32/819_2.png) [@Lukas\_Vlcek1](https://discuss.elastic.co/u/Lukas_Vlcek1)\ **Post date:** [July 20, 2010, 9:45pm UTC](https://discuss.elastic.co/t/query-for-null/3129/2 "2010-07-20T21:45:42Z")
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/) [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](mailto: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! ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [July 20, 2010, 10:03pm UTC](https://discuss.elastic.co/t/query-for-null/3129/3 "2010-07-20T22:03:14Z")
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](mailto: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! ---
**Author:** ![bailey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bailey/32/3278_2.png) [@bailey](https://discuss.elastic.co/u/bailey)\ **Post date:** [July 21, 2010, 1:35pm UTC](https://discuss.elastic.co/t/query-for-null/3129/4 "2010-07-21T13:35:58Z")
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](mailto: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:[Field data types | Elasticsearch Guide [8.11] | Elastic](http://www.elasticsearch.com/docs/elasticsearch/mapping/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](mailto: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! ---
**Author:** ![Lukas\_Vlcek1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas_vlcek1/32/819_2.png) [@Lukas\_Vlcek1](https://discuss.elastic.co/u/Lukas_Vlcek1)\ **Post date:** [July 21, 2010, 1:43pm UTC](https://discuss.elastic.co/t/query-for-null/3129/5 "2010-07-21T13:43:15Z")
Hi, On Wed, Jul 21, 2010 at 3:35 PM, bailey [matt.bailey@gmail.com](mailto: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](mailto: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: > > [Field data types | Elasticsearch Guide [8.11] | Elastic](http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/) > > \<[Field data types | Elasticsearch Guide [8.11] | Elastic](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](mailto: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 ---
**Author:** ![bailey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bailey/32/3278_2.png) [@bailey](https://discuss.elastic.co/u/bailey)\ **Post date:** [July 21, 2010, 1:54pm UTC](https://discuss.elastic.co/t/query-for-null/3129/6 "2010-07-21T13:54:12Z")
Thanks for the clarifications Lukas. On Jul 21, 9:43 am, Lukáš Vlček [lukas.vl...@gmail.com](mailto:lukas.vl...@gmail.com) wrote: > Hi, > > On Wed, Jul 21, 2010 at 3:35 PM, bailey [matt.bai...@gmail.com](mailto: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](mailto: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: > > > [Field data types | Elasticsearch Guide [8.11] | Elastic](http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/) > > > \<[Field data types | Elasticsearch Guide [8.11] | Elastic](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](mailto: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 ---
**Author:** ![ppearcy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ppearcy/32/980_2.png) [@ppearcy](https://discuss.elastic.co/u/ppearcy)\ **Post date:** [July 30, 2010, 10:58pm UTC](https://discuss.elastic.co/t/query-for-null/3129/7 "2010-07-30T22:58:33Z")
FYI, I raised a feature request issue for this: > **[Issues · elastic/elasticsearch](https://github.com/elastic/elasticsearch/issues#issue/281)** > > Free and Open, Distributed, RESTful Search Engine. Contribute to elastic/elasticsearch development by creating an account on GitHub. 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](mailto:matt.bai...@gmail.com) wrote: > Thanks for the clarifications Lukas. > > On Jul 21, 9:43 am, Lukáš Vlček [lukas.vl...@gmail.com](mailto:lukas.vl...@gmail.com) wrote: > > > Hi, > > > On Wed, Jul 21, 2010 at 3:35 PM, bailey [matt.bai...@gmail.com](mailto: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](mailto:lukas.vl...@gmail.com) wrote: > > > > > > > Hi, > > > > > I think it depends on how you defined mapping fornullvalue of that > > > > field. > > > > See mapping of core types: > > > > [Field data types | Elasticsearch Guide [8.11] | Elastic](http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/) > > > > \<[Field data types | Elasticsearch Guide [8.11] | Elastic](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](mailto: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 ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [August 1, 2010, 11:15am UTC](https://discuss.elastic.co/t/query-for-null/3129/8 "2010-08-01T11:15:23Z")
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](mailto:ppearcy@gmail.com) wrote: > FYI, I raised a feature request issue for this: > [Issues · elastic/elasticsearch · GitHub](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](mailto:matt.bai...@gmail.com) wrote: > > > Thanks for the clarifications Lukas. > > > > On Jul 21, 9:43 am, Lukáš Vlček [lukas.vl...@gmail.com](mailto:lukas.vl...@gmail.com) wrote: > > > > > Hi, > > > > > On Wed, Jul 21, 2010 at 3:35 PM, bailey [matt.bai...@gmail.com](mailto: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](mailto:lukas.vl...@gmail.com) wrote: > > > > > > > > > Hi, > > > > > > > I think it depends on how you defined mapping fornullvalue of that > > > > > field. > > > > > See mapping of core types: > > > > > [Field data types | Elasticsearch Guide [8.11] | Elastic](http://www.elasticsearch.com/docs/elasticsearch/mapping/core_types/) > > > > > \< > > > > > [Field data types | Elasticsearch Guide [8.11] | Elastic](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](mailto: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 ---
**Author:** ![elasticsearcher](https://avatars.discourse-cdn.com/v4/letter/e/7ba0ec/32.png) [@elasticsearcher](https://discuss.elastic.co/u/elasticsearcher)\ **Post date:** [October 21, 2010, 3:32pm UTC](https://discuss.elastic.co/t/query-for-null/3129/9 "2010-10-21T15:32:03Z")
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. ---
**Author:** ![ppearcy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ppearcy/32/980_2.png) [@ppearcy](https://discuss.elastic.co/u/ppearcy)\ **Post date:** [October 22, 2010, 12:30am UTC](https://discuss.elastic.co/t/query-for-null/3129/10 "2010-10-22T00:30:36Z")
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: > **[Issues · elastic/elasticsearch](https://github.com/elastic/elasticsearch/issues#issue/320)** > > Free and Open, Distributed, RESTful Search Engine. Contribute to elastic/elasticsearch development by creating an account on GitHub. Regards, Paul On Oct 21, 9:32 am, elasticsearcher [elasticsearc...@gmail.com](mailto: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](http://elasticsearch-users.115913.n3.nabble.com/Query-for-null-tp9826)... > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [October 22, 2010, 3:57pm UTC](https://discuss.elastic.co/t/query-for-null/3129/11 "2010-10-22T15:57:43Z")
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](mailto:ppearcy@gmail.com) 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: > [Issues · elastic/elasticsearch · GitHub](http://github.com/elasticsearch/elasticsearch/issues#issue/320) > > Regards, > Paul > > On Oct 21, 9:32 am, elasticsearcher [elasticsearc...@gmail.com](mailto: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](http://elasticsearch-users.115913.n3.nabble.com/Query-for-null-tp9826)... > > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). ---
**Author:** ![elasticsearcher](https://avatars.discourse-cdn.com/v4/letter/e/7ba0ec/32.png) [@elasticsearcher](https://discuss.elastic.co/u/elasticsearcher)\ **Post date:** [October 22, 2010, 4:57pm UTC](https://discuss.elastic.co/t/query-for-null/3129/12 "2010-10-22T16:57:23Z")
Awesome, thanks! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 4:17am UTC](https://discuss.elastic.co/t/query-for-null/3129/13 "2017-07-06T04:17:40Z")