Can't seem to get back correct results for non-null fields on 19.6


(psyg) #1

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 }
}
}
}

--


(Radu Gheorghe) #2

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 :slight_smile:

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/ -- ElasticSearch -- Solr -- Lucene

On Wed, Oct 24, 2012 at 3:23 AM, psyg 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 }
}
}
}

--

--


(psyg) #3

Thanks for the reply!

Sorry to say that code did not work for me :frowning: 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.comwrote:

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 :slight_smile:

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/ -- ElasticSearch -- Solr -- Lucene

On Wed, Oct 24, 2012 at 3:23 AM, psyg 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 }
}
}
}

--

--

--


(system) #4