Elastic Search - Sort by multiple fields with the missing parameter

Question

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.

Sounds like you didn't try the queries you are asking about... — Andrei Stefan
– Andrei Stefan, Commented Apr 18, 2016 at 21:14
I have tried the queries, the first set of sorts doesn't give an error, I am just curious if there is a better way to do it or if I am overlooking something. The second query does not work, but thats expected as I have not been able to find any documentation to support that format - its more of a pseudo code attempt to get the point across. — Paul
– Paul, Commented Apr 18, 2016 at 21:18
There is a missing option for sort: elastic.co/guide/en/elasticsearch/reference/current/… — Andrei Stefan
– Andrei Stefan, Commented Apr 18, 2016 at 21:20
@AndreiStefan Yeah, I saw the missing option, however I don't think that setting it to _first or _last is what I want. I was curious about the custom value but didn't see any other documentation about how to use that in this context. — Paul
– Paul, Commented Apr 18, 2016 at 21:22

Rob Swiston · Accepted Answer · 2017-02-27 20:52:27Z

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.

Stefan Rogin · Accepted Answer · 2017-10-30 14:45:01Z

3

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 ].

edited Oct 30, 2017 at 14:45

answered Aug 29, 2017 at 21:04

Stefan Rogin

1,5474 gold badges25 silver badges44 bronze badges

1 Comment

Ming Slogar Over a year ago

It seems your nullcheck logic is reversed here; shouldn't it be "inline": "doc[\u0027price_sold\u0027] != null ? doc[\u0027price_sold\u0027].value : doc[\u0027price_list\u0027].value"?

Collectives™ on Stack Overflow

Elastic Search - Sort by multiple fields with the missing parameter

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related