Elasticsearch conditional sorting

Question

I have a document with fields part1, part2, part3, part4, part5 which are parts of overall document business id consisting of chars and digits. Sometimes part1-3 is only present, sometimes part4-5, and sometimes all parts. So for example document can have business id:

• 12 AR 1023 where there are three parts: number (part1), string (part2), number (part3)
• XX 421 where there are two parts: string (part4), number (part5)

So in summary this id can have different format and I have to sort those documents. Whats important I cannot concatenate those fields because I would sort by strings, I need to sort them separately because there are numbers and each number field has to be sorted individually, as well as each string part. That means I always need to provide list of fields to sort. The algorithm is: if part4 and part5 are present, sort by part4, part5, otherwise sort by part1, part2, part3.

Is it possible to create a query that would sort all documents in such conditional way ? Or do I need to sort them by part1,part2,part3,part4,part5. In this approach I would sort by part1-3 in the beginning which is wrong, because I need to sort by part4-5 if they are present and omit other parts. But when part4-5 are saved into document I could remove other parts so they would be transparent for all documents having part4-5 only (if part4-5 are present, parts1-3 looses its importance and can be removed if present)

What approach could i use ?

Answer 1

In Elasticsearch, you can achieve conditional sorting using a script-based sorting approach.

Create a script that evaluates the presence of part4 and part5 in each document, sort the documents based on the result of the script. If part4 and part5 are present, sort by part4 and part5; otherwise, sort by part1, part2, and part3.

{
  "query": {
    "match_all": {}
  },
  "sort": [
    {
      "_script": {
        "script": {
          "lang": "painless",
          "source": """
            if (doc['part4.keyword'].size() > 0 && doc['part5.keyword'].size() > 0) {
              return [doc['part4.keyword'].value, doc['part5.keyword'].value];
            } else {
              return [doc['part1.keyword'].value, doc['part2.keyword'].value, doc['part3.keyword'].value];
            }
          """
        },
        "order": "asc"
      }
    }
  ]
}