Checking for null numeric values

kamil_2 · September 15, 2012, 1:04am

I've got a query I'm running with a script-based sort:

{
"from":0,
"size":10,
"query": {
"constant_score": {
"filter": {
"term": { "slice_id": 2 }
},
},
},
"sort": {
"_script": {
"script":
"field=doc['magnitude'].value;return(field==null)?2147483647:field;",
"type": "number",
"order": "asc"
}
}
}

That is, I'm trying to sort the documents by the "magnitude" field. If the
field is null then I want those values pushed to the bottom of the result
set. Here's the field definition:

"magnitude": {
"type": "long",
"include_in_all": false
}

I'm finding that the sort applies a value of "0.0" to all records where the
magnitude field is null. Here's an example of one of the results in the set:

{
"_index" : "data_201209061123",
"_type" : "contact",
"_id" : "5365",
"_score" : null,
"_source" : {
"contact_id":5365,
"country":"US",
"city":"Rochester",
"slice_id":2
},
"sort" : [ 0.0 ]
}

As you can see, there is no "magnitude" field on this record. I would
expect that the script I am using will detect this as a null field and
assign it a score of 2147483647. Is there something wrong with my script?
Any hints will be appreciated. Thanks.

Kamil

--

kamil_2 · September 17, 2012, 5:26pm

I never did figure out why my null long fields are coming back with a value
of 0.0 but I worked around the issue by changing my script to do the
following:

field = doc['magnitude'];
return (field.values.length == 0) ? 2147483647 : field.value;

Kamil

--

Chris_Male · September 18, 2012, 4:20am

Kamil,

Would an alternative be to use sort missing last? If you take a look at
Elasticsearch Platform — Find real-time answers at scale | Elastic under
Missing Values you can control how numeric fields with missing values are
handled, by either pushing them to first or last, or assigning them a
default value.

On Tuesday, September 18, 2012 5:26:19 AM UTC+12, Kamil wrote:

I never did figure out why my null long fields are coming back with a
value of 0.0 but I worked around the issue by changing my script to do the
following:

field = doc['magnitude'];
return (field.values.length == 0) ? 2147483647 : field.value;

Kamil

--

kimchy · September 18, 2012, 10:16am

Another option that would be slightly more performant is to use field.empty ? …

On Sep 17, 2012, at 7:26 PM, Kamil kamil.jiwa@gmail.com wrote:

I never did figure out why my null long fields are coming back with a value of 0.0 but I worked around the issue by changing my script to do the following:

field = doc['magnitude'];
return (field.values.length == 0) ? 2147483647 : field.value;

Kamil

--

--

Kyle_Lahnakoski · September 18, 2012, 4:16pm

Docs at
http://www.elasticsearch.org/guide/reference/modules/scripting.html say
" In order to change it set the |script.default_lang| to the appropriate
language", but I do not know the where to put that assignment.

I tried

var query : {
    "script" : { default_lang" : "lang-javascipt"},
    "query" : {
...

but that does not work. May someone give me an example of a es query
that uses javascript?

PS. I hope to use javascript in a facet to loop through children of a
document to find the one value to groupby on.

"facets" : {
    "request" : {
        "terms" : {
            "script" : "function(){for(i in _source.attachments){ if
(_source.attachments[i].name) return (_source.attachments[i].name;}}"
            "size" : 10000
        }
    }
}

--
Kyle Lahnakoski
Arcavia Software Ltd.

Mobile:647 834 8431
Email:kyle@arcavia.com mailto:kyle@arcavia.com

--

kamil_2 · September 18, 2012, 5:50pm

Thanks Chris and Shay. I tried both solutions and found that "field.empty"
does suit my use case, but that there is a negligible difference in
performance compared to "field.values.length == 0". However, I found that
using "missing": "_last" vastly outperforms the script solution. Here are
the timing results from 10 runs of each solution:

field.values.length:
- median: 0.0295s
- mean: 0.0280s
- stdev: 0.0029s
field.empty:
- median: 0.0300s
- mean: 0.0300s
- stdev: 0.0067s
"missing": "_last":
- 0.0110s
- 0.0110s
- 0.0007s

Thanks.

Kamil

On Tuesday, September 18, 2012 3:16:37 AM UTC-7, kimchy wrote:

Another option that would be slightly more performant is to use
field.empty ? …

On Sep 17, 2012, at 7:26 PM, Kamil <kamil...@gmail.com <javascript:>>
wrote:

I never did figure out why my null long fields are coming back with a
value of 0.0 but I worked around the issue by changing my script to do the
following:

field = doc['magnitude'];
return (field.values.length == 0) ? 2147483647 : field.value;

Kamil

--

--

kamil_2 · September 18, 2012, 5:51pm

Thanks Chris and Shay. I tried both solutions and found that "field.empty"
does suit my use case, but that there is a negligible difference in
performance compared to "field.values.length == 0". However, I found that
using "missing": "_last" vastly outperforms the script solution. Here are
the timing results from 10 runs of each solution:

field.values.length:
- median: 0.0295s
- mean: 0.0280s
- stdev: 0.0029s
field.empty:
- median: 0.0300s
- mean: 0.0300s
- stdev: 0.0067s
"missing": "_last":
- median: 0.0110s
- mean: 0.0110s
- stdev: 0.0007s

Thanks.

Kamil

On Tuesday, September 18, 2012 3:16:37 AM UTC-7, kimchy wrote:

Another option that would be slightly more performant is to use
field.empty ? …

On Sep 17, 2012, at 7:26 PM, Kamil <kamil...@gmail.com <javascript:>>
wrote:

I never did figure out why my null long fields are coming back with a
value of 0.0 but I worked around the issue by changing my script to do the
following:

field = doc['magnitude'];
return (field.values.length == 0) ? 2147483647 : field.value;

Kamil

--

--

kimchy · September 18, 2012, 7:53pm

This is a "node" level configuration, you set it in the elasticsearch.yml file under config. It will mean that if one doesn't specify a lang for the script, the default lang configured will be used.

On Sep 18, 2012, at 6:16 PM, Kyle Lahnakoski kyle@arcavia.com wrote:

Docs at Elasticsearch Platform — Find real-time answers at scale | Elastic say " In order to change it set the script.default_lang to the appropriate language", but I do not know the where to put that assignment.

I tried
var query : {
"script" : { default_lang" : "lang-javascipt"},
"query" : {
...
but that does not work. May someone give me an example of a es query that uses javascript?

PS. I hope to use javascript in a facet to loop through children of a document to find the one value to groupby on.

"facets" : {
"request" : {
"terms" : {
"script" : "function(){for(i in _source.attachments){ if (_source.attachments[i].name) return (_source.attachments[i].name;}}"
"size" : 10000
}
}
}

--
Kyle Lahnakoski
Arcavia Software Ltd.

Mobile:647 834 8431
Email:kyle@arcavia.com

--

--

Kyle_Lahnakoski · September 20, 2012, 5:56pm

Thank you, but I have no access to the the *.yml files.

My question now regarding the "lang" tag.

Thanks again.

On 9/18/2012 3:53 PM, Shay Banon wrote:

This is a "node" level configuration, you set it in the
elasticsearch.yml file under config. It will mean that if one doesn't
specify a lang for the script, the default lang configured will be used.

On Sep 18, 2012, at 6:16 PM, Kyle Lahnakoski <kyle@arcavia.com
mailto:kyle@arcavia.com> wrote:
Docs at
Elasticsearch Platform — Find real-time answers at scale | Elastic
say " In order to change it set the |script.default_lang| to the
appropriate language", but I do not know the where to put that
assignment.

I tried
var query : {
    "script" : { default_lang" : "lang-javascipt"},
    "query" : {
...
but that does not work. May someone give me an example of a es
query that uses javascript?

PS. I hope to use javascript in a facet to loop through children of
a document to find the one value to groupby on.
"facets" : {
    "request" : {
        "terms" : {
            "script" : "function(){for(i in _source.attachments){
if (_source.attachments[i].name) return
(_source.attachments[i].name;}}"
            "size" : 10000
        }
    }
}
--
Kyle Lahnakoski
Arcavia Software Ltd.

Mobile:647 834 8431
Email:kyle@arcavia.com mailto:kyle@arcavia.com

--
--

--
http://www.linkedin.com/in/kylelahnakoski
Kyle Lahnakoski
Arcavia Software Ltd.

Mobile:647 834 8431
Email:kyle@arcavia.com mailto:kyle@arcavia.com

--

Topic		Replies	Views
Sorting of numeric 'null' fields Elasticsearch	3	3976	July 6, 2017
Null values in number field in Elasticsearch Elasticsearch	1	667	March 16, 2017
Sort ascending with null values last Elasticsearch	4	5781	July 6, 2017
Score null when sorting results Elastic Search elastic-app-search	4	2377	September 21, 2020
Script sorting retun type exception Elasticsearch	4	650	September 25, 2020

Checking for null numeric values

Related Topics