# Can't seem to get back correct results for non-null fields on 19.6 **URL:** \ **Category:** Elasticsearch\ **Created:** [October 24, 2012, 12:23am UTC](https://discuss.elastic.co/t/cant-seem-to-get-back-correct-results-for-non-null-fields-on-19-6/9452 "2012-10-24T00:23:19Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![psyg](https://avatars.discourse-cdn.com/v4/letter/p/f4b2a3/32.png) [@psyg](https://discuss.elastic.co/u/psyg)\ **Post date:** [October 24, 2012, 12:23am UTC](https://discuss.elastic.co/t/cant-seem-to-get-back-correct-results-for-non-null-fields-on-19-6/9452/1 "2012-10-24T00:23:19Z")
Hello, I'm trying to get back all the non-null/existing values for a field and used the following, which works great on ES 19.9 but our production env is using 19.6 and on the older version this code returns ALL the documents. Any ideas or even some alternate code to use? { 'filtered' : { 'filter' : { 'exists' : {'field' : column } }, 'query' : { 'match\_all' : {} } } } My goal here is to create a list of all the distinct values for a single field across all indicies. I've tried the following as well. It's worth mentioning I am doing several other queries, mainly filtered but everything else seems to work. Hoping is just something simple I'm missing but could really use some help. Thanks all! { "constant\_score" : { "filter" : { "missing" : { "field" : column } } } } AND { "constant\_score" : { "filter" : { "exists" : { "field" : column } } } } -- ---
**Author:** ![radu\_gheorghe](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/radu_gheorghe/32/556_2.png) [@radu\_gheorghe](https://discuss.elastic.co/u/radu_gheorghe)\ **Post date:** [October 24, 2012, 12:11pm UTC](https://discuss.elastic.co/t/cant-seem-to-get-back-correct-results-for-non-null-fields-on-19-6/9452/2 "2012-10-24T12:11:39Z")
Hello, This is rather weird, because I've just tried with 0.19.6 and for me it works. Does the following work for you? { "filter": { "not": { "missing": { "field": "FIELD\_NAME\_GOES\_HERE" } } } } But if you have enough memory, you can try a facet to get you the distinct values. And you'll get their count as a bonus 🙂 Something like: curl -XPOST localhost:9200/\_search?pretty=true -d '{ "query": { "match\_all": {} }, "facets": { "distinct\_values": { "terms": { "script\_field": "\_source.FIELD\_NAME\_GOES\_HERE", "size": 1000 } } } }' Where 1000 would be the maximum number of values returned. If you have more values than you set your facet to return, they will be counted in the "other" field. And the number of "missing" values is counted in the "missing" field. ## Best regards, Radu [http://sematext.com/](http://sematext.com/) -- Elasticsearch -- Solr -- Lucene On Wed, Oct 24, 2012 at 3:23 AM, psyg [mnsolace@gmail.com](mailto:mnsolace@gmail.com) wrote: > Hello, I'm trying to get back all the non-null/existing values for a field > and used the following, which works great on ES 19.9 but our production env > is using 19.6 and on the older version this code returns ALL the documents. > Any ideas or even some alternate code to use? > > { > 'filtered' : { > 'filter' : { > 'exists' : {'field' : column } > }, > 'query' : { > 'match\_all' : {} > } > } > } > > My goal here is to create a list of all the distinct values for a single > field across all indicies. I've tried the following as well. It's worth > mentioning I am doing several other queries, mainly filtered but everything > else seems to work. Hoping is just something simple I'm missing but could > really use some help. Thanks all! > > { > "constant\_score" : { > "filter" : { > "missing" : { "field" : column } > } > } > } > > AND > > { > "constant\_score" : { > "filter" : { > "exists" : { "field" : column } > } > } > } > > -- -- ---
**Author:** ![psyg](https://avatars.discourse-cdn.com/v4/letter/p/f4b2a3/32.png) [@psyg](https://discuss.elastic.co/u/psyg)\ **Post date:** [October 24, 2012, 3:34pm UTC](https://discuss.elastic.co/t/cant-seem-to-get-back-correct-results-for-non-null-fields-on-19-6/9452/3 "2012-10-24T15:34:24Z")
Thanks for the reply! Sorry to say that code did not work for me ☹ I ended up using the following: query = { "filtered" : { "query" : { "match\_all" : {} }, "filter" : { "not" : { "term" : { "column" : "null" } } } } } and then defining 'null' as a string in my index. "column": { "type": "string", "store": "yes", "index": "analyzed", "null\_value": "null" } I'm very new to elasticsearch, so this may very well be something I am doing incorrectly. But it seems to be very fast as it returns everything that has a value for that column other than 'null' and I'm able to sort the results in memory easily enough. I would like to be able to do this and sort the values all in the elasticpath query however... Thanks! On Wed, Oct 24, 2012 at 6:11 AM, Radu Gheorghe [radu.gheorghe@sematext.com](mailto:radu.gheorghe@sematext.com)wrote: > Hello, > > This is rather weird, because I've just tried with 0.19.6 and for me > it works. Does the following work for you? > > { > "filter": { > "not": { > "missing": { > "field": "FIELD\_NAME\_GOES\_HERE" > } > } > } > } > > But if you have enough memory, you can try a facet to get you the > distinct values. And you'll get their count as a bonus 🙂 > > Something like: > > curl -XPOST localhost:9200/\_search?pretty=true -d '{ > "query": { > "match\_all": {} > }, > "facets": { > "distinct\_values": { > "terms": { > "script\_field": "\_source.FIELD\_NAME\_GOES\_HERE", > "size": 1000 > } > } > } > }' > > Where 1000 would be the maximum number of values returned. If you have > more values than you set your facet to return, they will be counted in > the "other" field. And the number of "missing" values is counted in > the "missing" field. > > ## Best regards, Radu > > [http://sematext.com/](http://sematext.com/) -- Elasticsearch -- Solr -- Lucene > > On Wed, Oct 24, 2012 at 3:23 AM, psyg [mnsolace@gmail.com](mailto:mnsolace@gmail.com) wrote: > > > Hello, I'm trying to get back all the non-null/existing values for a > > field > > and used the following, which works great on ES 19.9 but our production > > env > > is using 19.6 and on the older version this code returns ALL the > > documents. > > Any ideas or even some alternate code to use? > > > > { > > 'filtered' : { > > 'filter' : { > > 'exists' : {'field' : column } > > }, > > 'query' : { > > 'match\_all' : {} > > } > > } > > } > > > > My goal here is to create a list of all the distinct values for a single > > field across all indicies. I've tried the following as well. It's worth > > mentioning I am doing several other queries, mainly filtered but > > everything > > else seems to work. Hoping is just something simple I'm missing but could > > really use some help. Thanks all! > > > > { > > "constant\_score" : { > > "filter" : { > > "missing" : { "field" : column } > > } > > } > > } > > > > AND > > > > { > > "constant\_score" : { > > "filter" : { > > "exists" : { "field" : column } > > } > > } > > } > > > > -- > > -- -- ---
**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, 3:07am UTC](https://discuss.elastic.co/t/cant-seem-to-get-back-correct-results-for-non-null-fields-on-19-6/9452/4 "2017-07-06T03:07:25Z")