# Querying ElasticSearch to order empty strings last

**URL:** <https://discuss.elastic.co/t/querying-elasticsearch-to-order-empty-strings-last/10080>\
**Category:** Elasticsearch\
**Created:** [December 15, 2012, 8:43pm UTC](https://discuss.elastic.co/t/querying-elasticsearch-to-order-empty-strings-last/10080 "2012-12-15T20:43:32Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Kevin\_Tran](https://avatars.discourse-cdn.com/v4/letter/k/439d5e/32.png) [@Kevin\_Tran](https://discuss.elastic.co/u/Kevin_Tran)\
**Post date:** [December 15, 2012, 8:43pm UTC](https://discuss.elastic.co/t/querying-elasticsearch-to-order-empty-strings-last/10080/1 "2012-12-15T20:43:32Z")

</div>

I am using Django, Haystack, and ElasticSearch. I want to order my search  
results so that results where the ordered field value is empty ("") come  
after results where it is not empty. I cannot find an API in Haystack that  
can do this. The request sent to ElasticSearch looks like:

```
{
   "sort":[
      {
         "version":{
            "order":"asc"
         }
      }
   ],
   "query":{
      ...
   }
}

```

Is there a way to rewrite this ElasticSearch query so that results with an  
empty string for "version" will come after results where "version" exists?

I have implemented this in Python as:

```
sorted(sqs, key=lambda x: getattr(x, 'version') == '')

```

--

---

<div class="post-metadata">

**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:** [December 17, 2012, 12:08pm UTC](https://discuss.elastic.co/t/querying-elasticsearch-to-order-empty-strings-last/10080/2 "2012-12-17T12:08:10Z")

</div>

Hello Kevin,

Here's something that should work, but it's going to be slow:

```
"sort": {
    "_script": {
        "script": "if (_source.version == \"\") { \"zzz\" } else {

```

\_source.version }",  
"type": "string",  
"order": "asc"  
}  
}

If you want it fast, I think you should look at indexing your versions as  
numbers. Then you can use "missing" to put docs without a "version" field  
either at the beginning or at the end:

> **[Elastic — The Search AI Company](https://www.elastic.co)**
>
> Power insights and outcomes with The Elastic Search AI Platform. See into your data and find answers that matter with enterprise solutions designed to help you accelerate time to insight. Try Elastic ...

In case your versioning format can't be easily indexed as numbers (because  
of multiple dots), you can index parts in different fields. And then you  
can sort on all of them by specifying an array there.

## Best regards, Radu

[http://sematext.com/](http://sematext.com/) -- Elasticsearch -- Solr -- Lucene

On Sat, Dec 15, 2012 at 10:43 PM, Kevin Tran [hekevintran@gmail.com](mailto:hekevintran@gmail.com) wrote:

> I am using Django, Haystack, and Elasticsearch. I want to order my search  
> results so that results where the ordered field value is empty ("") come  
> after results where it is not empty. I cannot find an API in Haystack that  
> can do this. The request sent to Elasticsearch looks like:
> 
> ```
> {
> "sort":[
> {
> "version":{
> "order":"asc"
> }
> }
> ],
> "query":{
> ...
> }
> }
> 
> ```
> 
> Is there a way to rewrite this Elasticsearch query so that results with an  
> empty string for "version" will come after results where "version" exists?
> 
> I have implemented this in Python as:
> 
> ```
> sorted(sqs, key=lambda x: getattr(x, 'version') == '')
> 
> ```
> 
> --

--

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [December 17, 2012, 7:07pm UTC](https://discuss.elastic.co/t/querying-elasticsearch-to-order-empty-strings-last/10080/3 "2012-12-17T19:07:28Z")

</div>

Hi Kevin,

Here is a faster version of the query:

{  
"query": {  
"custom\_filters\_score" : {  
"query" : {  
"constant\_score": {  
"query": {  
.... your original query ....

```
                }
            }
        },
        "filters" : [
            {
                "filter" : { "missing" : { "field" : "version"} },
                "boost" : "2"
            }
        ]
    }        
},
"sort": [
    {
        "_score": {"order":"asc"}
    },
    {
        "version": {"order":"asc"}
    }
]

```

}

It basically assigns \_score of 1.0 to all records with non-empty version  
and \_score of 2.0 to all records with empty version. Then it sorts by  
\_score in ascending order and then by version in ascending order. As a  
result, all records with empty version are pushed to the bottom of the  
list.

Igor

On Monday, December 17, 2012 4:08:10 AM UTC-8, Radu Gheorghe wrote:

> Hello Kevin,
> 
> Here's something that should work, but it's going to be slow:
> 
> ```
> "sort": {
> "_script": {
> "script": "if (_source.version == \"\") { \"zzz\" } else { 
> 
> ```
> 
> \_source.version }",  
> "type": "string",  
> "order": "asc"  
> }  
> }
> 
> If you want it fast, I think you should look at indexing your versions as  
> numbers. Then you can use "missing" to put docs without a "version" field  
> either at the beginning or at the end:  
> [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/sort.html)
> 
> In case your versioning format can't be easily indexed as numbers (because  
> of multiple dots), you can index parts in different fields. And then you  
> can sort on all of them by specifying an array there.
> 
> ## Best regards, Radu
> 
> [http://sematext.com/](http://sematext.com/) -- Elasticsearch -- Solr -- Lucene
> 
> On Sat, Dec 15, 2012 at 10:43 PM, Kevin Tran \<[hekev...@gmail.com](mailto:hekev...@gmail.com)\<javascript:\>
> 
> > wrote:
> 
> > I am using Django, Haystack, and Elasticsearch. I want to order my search  
> > results so that results where the ordered field value is empty ("") come  
> > after results where it is not empty. I cannot find an API in Haystack that  
> > can do this. The request sent to Elasticsearch looks like:
> > 
> > ```
> > {
> > "sort":[
> > {
> > "version":{
> > "order":"asc"
> > }
> > }
> > ],
> > "query":{
> > ...
> > }
> > }
> > 
> > ```
> > 
> > Is there a way to rewrite this Elasticsearch query so that results with  
> > an empty string for "version" will come after results where "version"  
> > exists?
> > 
> > I have implemented this in Python as:
> > 
> > ```
> > sorted(sqs, key=lambda x: getattr(x, 'version') == '')
> > 
> > ```
> > 
> > --

--

---

<div class="post-metadata">

**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, 2:59am UTC](https://discuss.elastic.co/t/querying-elasticsearch-to-order-empty-strings-last/10080/4 "2017-07-06T02:59:36Z")

</div>