# Checking for null numeric values **URL:** \ **Category:** Elasticsearch\ **Created:** [September 15, 2012, 1:04am UTC](https://discuss.elastic.co/t/checking-for-null-numeric-values/9032 "2012-09-15T01:04:59Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![kamil\_2](https://avatars.discourse-cdn.com/v4/letter/k/90ced4/32.png) [@kamil\_2](https://discuss.elastic.co/u/kamil_2)\ **Post date:** [September 15, 2012, 1:04am UTC](https://discuss.elastic.co/t/checking-for-null-numeric-values/9032/1 "2012-09-15T01:04:59Z")
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 -- ---
**Author:** ![kamil\_2](https://avatars.discourse-cdn.com/v4/letter/k/90ced4/32.png) [@kamil\_2](https://discuss.elastic.co/u/kamil_2)\ **Post date:** [September 17, 2012, 5:26pm UTC](https://discuss.elastic.co/t/checking-for-null-numeric-values/9032/2 "2012-09-17T17:26:18Z")
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 -- ---
**Author:** ![Chris\_Male](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/chris_male/32/2607_2.png) [@Chris\_Male](https://discuss.elastic.co/u/Chris_Male)\ **Post date:** [September 18, 2012, 4:20am UTC](https://discuss.elastic.co/t/checking-for-null-numeric-values/9032/3 "2012-09-18T04:20:13Z")
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](http://www.elasticsearch.org/guide/reference/api/search/sort.html) 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 -- ---
**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:** [September 18, 2012, 10:16am UTC](https://discuss.elastic.co/t/checking-for-null-numeric-values/9032/4 "2012-09-18T10:16:36Z")
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](mailto: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 > > -- -- ---
**Author:** ![Kyle\_Lahnakoski](https://avatars.discourse-cdn.com/v4/letter/k/bcef8e/32.png) [@Kyle\_Lahnakoski](https://discuss.elastic.co/u/Kyle_Lahnakoski)\ **Post date:** [September 18, 2012, 4:16pm UTC](https://discuss.elastic.co/t/checking-for-null-numeric-values/9032/5 "2012-09-18T16:16:56Z")
Docs at [http://www.elasticsearch.org/guide/reference/modules/scripting.html](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:Email:kyle@arcavia.com) [mailto:kyle@arcavia.com](mailto:kyle@arcavia.com) -- ---
**Author:** ![kamil\_2](https://avatars.discourse-cdn.com/v4/letter/k/90ced4/32.png) [@kamil\_2](https://discuss.elastic.co/u/kamil_2)\ **Post date:** [September 18, 2012, 5:50pm UTC](https://discuss.elastic.co/t/checking-for-null-numeric-values/9032/6 "2012-09-18T17:50:24Z")
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: - field.empty: - "missing": "\_last": 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](mailto:kamil...@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 > > -- -- ---
**Author:** ![kamil\_2](https://avatars.discourse-cdn.com/v4/letter/k/90ced4/32.png) [@kamil\_2](https://discuss.elastic.co/u/kamil_2)\ **Post date:** [September 18, 2012, 5:51pm UTC](https://discuss.elastic.co/t/checking-for-null-numeric-values/9032/7 "2012-09-18T17:51:00Z")
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: - field.empty: - "missing": "\_last": 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](mailto:kamil...@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 > > -- -- ---
**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:** [September 18, 2012, 7:53pm UTC](https://discuss.elastic.co/t/checking-for-null-numeric-values/9032/8 "2012-09-18T19:53:13Z")
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](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:Email:kyle@arcavia.com) > > -- -- ---
**Author:** ![Kyle\_Lahnakoski](https://avatars.discourse-cdn.com/v4/letter/k/bcef8e/32.png) [@Kyle\_Lahnakoski](https://discuss.elastic.co/u/Kyle_Lahnakoski)\ **Post date:** [September 20, 2012, 5:56pm UTC](https://discuss.elastic.co/t/checking-for-null-numeric-values/9032/9 "2012-09-20T17:56:51Z")
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) > [mailto:kyle@arcavia.com](mailto:kyle@arcavia.com)\> wrote: > > > Docs at > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](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:Email:kyle@arcavia.com) [mailto:kyle@arcavia.com](mailto:kyle@arcavia.com) > > > > -- > > -- -- [http://www.linkedin.com/in/kylelahnakoski](http://www.linkedin.com/in/kylelahnakoski) _Kyle Lahnakoski_ Arcavia Software Ltd. Mobile:647 834 8431 [Email:kyle@arcavia.com](mailto:Email:kyle@arcavia.com) [mailto:kyle@arcavia.com](mailto:kyle@arcavia.com) -- ---
**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:12am UTC](https://discuss.elastic.co/t/checking-for-null-numeric-values/9032/10 "2017-07-06T03:12:06Z")