CODEWITHSUNDEEP
elasticsearch
Fabio Cicerchia
Fabio Cicerchia

Reputation: 679

Elasticsearch order by a certain field value first

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

Upvotes: 10

Views: 16575

Answers (2)

Adrian
Adrian

Reputation: 101

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.

Upvotes: 10

Gentle Y
Gentle Y

Reputation: 331

Actually you want to sort by city first then by price ? The key point is the type of "city" filed , it's string (maybe you set analyzed ? ) not integer. So if you set analyzed on filed "city", you'd better add a property named "raw" without analyzed, then you can sort by "city: first .

Try the following:

 "sort": [{"city.raw": {"order": "asc"}},{"price": {"order": "asc"}}],

You can visit https://www.elastic.co/guide/en/elasticsearch/guide/current/multi-fields.html for more help.

Otherwise, if you only need to make the "Liverpool" city first then other cities when search and sort , you should use boost feature in your query. Visit https://www.elastic.co/guide/en/elasticsearch/guide/2.x/multi-query-strings.html for more help.

Upvotes: 1

Related Questions