CODEWITHSUNDEEP
sortingelasticsearchfilter
Paul
Paul

Reputation: 401

Elastic Search - Sort by multiple fields with the missing parameter

I am trying to apply a sort to an Elastic Search query by two different fields:

price_sold and price_list

I would like to first sort on price_sold, but if that value is null, I would like to then sort by price_list

Would the query be correct if I just set the sorts to:

"sort": [
 { "price_sold": { "order": "desc"}},
 { "price_list": { "order": "desc"}}
]

I have executed the query, and I do not get any errors, and it seems like the results are correct, however I am curious if I have overlooked something.

I have been reading about the missing filter, along with possibly using a custom value. This may not be required, but I am not quite sure.

Would there be a way to define a second field to sort on if the first field is missing, or is that not necessary? Something like:

"sort": [{"price_sold: {"order": "desc", "missing": "doc['field_name']"}]

Would simply adding these two sorts give me the desired result?

Thanks.

Upvotes: 2

Views: 5536

Answers (2)

Stefan Rogin
Stefan Rogin

Reputation: 1527

In case someone is still looking I ended up creating a script similar to this: 

curl -XGET 'localhost:9200/_search?pretty&size=10&from=0' -H 'Content-Type: application/json' -d'
{
    "sort" : {
        "_script" : {
            "type" : "number",
            "script" : {
                "lang": "painless",
                "inline": "doc[\u0027price_sold\u0027] == null ? doc[\u0027price_list\u0027].value : doc[\u0027price_sold\u0027].value"
            },
            "order" : "desc"
        }
    },
}
'

For sorting dates, the type still has to remain number but you replace .value with .date.getMillisOfDay() as discussed here.

The from and size worked fine in my version of ElasticSearch (5.1.1). To make sure your algorithm is working fine check the generated value in the response, e.g.: "sort" : [ 5.0622E7 ].

Upvotes: 3

Rob Swiston
Rob Swiston

Reputation: 31

I think I understand what you're asking. In SQL terms, you'd like to ORDER BY COALESCE(price_sold, price_list) DESC.

The first sort you listed is a little different. It's similar to ORDER BY price_sold DESC, price_list DESC - in other words, primary sort is by price_sold, and for entries where price_sold is equal, secondary sort is by price_list.

Your second sort attempt would be great if "missing" worked that way. Unfortunately, missing's "custom" option appears to allow you to specify a constant value only.

If you don't need to limit your search using from and size, you should be able to use sort's _script option to write some logic that works for you. I ended up here because I do use from and size to retrieve batches, and when I sort by _script, the items I'm getting don't make sense - the items are sorted correctly, but I'm not getting the right set of items. So, I added a new analyzer and expanded my fields to use the new analyzer, and I was hoping to be able to sort using the new field or, if the new field doesn't exist (for previously-indexed items), use the old field's value instead. But that doesn't seem to be possible. I think I'm going to have to reindex my items so my new field is populated.

Upvotes: 3

Related Questions