Deepak
Deepak

Reputation: 511

Elastic Search - Sort - Switch between integer fields on 0 condition

Say I have two double datatype fields called priority1 and priority2. We need to sort on priority1, but when priority1 is 0 then choose priority2 field.

Example -

Document 1 - priority1 : 8.5, priority2 : 9.0
Document 2 - priority1 : 5.5, priority2 : 6.0
Document 3 - priority1 : 0, priority2 : 9.0
Document 4 - priority1 : 8.0, priority2 : 8.5
Document 5 - priority1 : 0, priority2 : 7.5

Documents should be sorted in order -

Document 2  (5.5)
Document 5  (7.5)
Document 4  (8.0)
Document 1  (8.5)
Document 3  (9.0)

Upvotes: 1

Views: 591

Answers (1)

ThomasC
ThomasC

Reputation: 8175

You can achieve this using a custom score script.

The following request :

POST test/scores/_search
{
  "sort":{
    "_script":{
      "lang":"groovy",
      "script" : "doc['priority1'].value == 0 ? doc['priority2'].value : doc['priority1'].value",
      "type" : "number",
      "order" : "asc"
    }
  }
}

will give you the order you want :

hits": [
         {
            "_index": "test",
            "_type": "scores",
            "_id": "7uyu0prrT-SCJFvwSVUI0Q",
            "_score": null,
            "_source": {
               "p1": 5.5,
               "p2": 6
            },
            "sort": [
               5.5
            ]
         },
         {
            "_index": "test",
            "_type": "scores",
            "_id": "mRjUlMHvQ4-Dj-BKVL1Oyg",
            "_score": null,
            "_source": {
               "p1": 0,
               "p2": 7.5
            },
            "sort": [
               7.5
            ]
         },
         {
            "_index": "test",
            "_type": "scores",
            "_id": "QKCK2G9GT0y6FzwAvu5p5A",
            "_score": null,
            "_source": {
               "p1": 8,
               "p2": 8.5
            },
            "sort": [
               8
            ]
         },
         {
            "_index": "test",
            "_type": "scores",
            "_id": "4-kUu3gDSTON9F6bd3ieRw",
            "_score": null,
            "_source": {
               "p1": 8.5,
               "p2": 9
            },
            "sort": [
               8.5
            ]
         },
         {
            "_index": "test",
            "_type": "scores",
            "_id": "KOqf6bhrTK2JioRFEppcjw",
            "_score": null,
            "_source": {
               "p1": 0,
               "p2": 9
            },
            "sort": [
               9
            ]
         }
      ]

You can have better performance using directly a function_score query (documentation here) and reusing the same script in the query.

Upvotes: 2

Related Questions