Elasticsearch order by a certain field value first

Question

I'd like to apply a certain sort to a query, it should sort my documents by a single value first then all the others. I need to achieve something like the ORDER BY CASE WHEN in MySQL, but I couldn't find how to do it.

Each element in the index in Elastic has the following structure:

{
    "id": 123,
    "name": "Title",
    "categories": ["A", "B", "C"],
    "price": 100,
    "city": "London",
    "country": "United Kingdom",
    "status": 1
}

I do the following query:

{
    "fields": [],
    "sort": [{"price": {"order": "asc"}}],
    "size": 0,
    "query": {
        "query_string": {
            "query": "status:1 AND country:'United Kingdom'"
        }
    },
    "aggs": {
        "id": {
            "terms": {
                "field": "id",
                "size": 10
            }
        }
    }
}

So by sorting the column city with value "Liverpool" first and considering the following example:

{"id": 1, "name": "Test", "categories": ["A", "B", "C"], "price": 100, "city": "London", "country": "United Kingdom", "status": 1 }
{"id": 2, "name": "Sample", "categories": ["A", "D", "F"], "price": 200, "city": "Manchester", "country": "United Kingdom", "status": 1 }
{"id": 3, "name": "Title", "categories": ["X", "Y", "Z"], "price": 1000, "city": "Liverpool", "country": "United Kingdom", "status": 1 }

I expect to have as output the following id: 3, 1, 2.

How can I change my query to obtain this behaviour?

UPDATE: The version is 1.7.2

Answer 1

You should use "_script" for 1.7 version. Try this:

1.7:

"query" : {
    ....
},
"sort" : {
    "_script" : {
        "script" : "doc['city'].value == 'Liverpool' ? 1 : 0",
        "type" : "number",
        "order" : "desc"
    },
    "example_other_field_order":"asc",
    "next_example_field_order":"asc"
}

For latest version of elasticsearch (>= 5.5) check this doc.