Sort based on absolute value

Elastic Stack Elasticsearch
1

Hi, I'm looking to sort documents based on a field of long type by their absolute value.

So, the change field has both positive and negative numbers.

"change" : {
"type" : "long"
}

I want to sort it in such a way that if change = -23 and change = 23, -23 gets the preference, but if change = 45 and 50, 50 gets the preference.

2

Hi @_baba

Set the "order" not work?

    "sort":[
        {
        "change":  {"order" : "desc"}
        }
    ]
3

for the data sets [-23, 23, 45, 50], this query will result [50, 45, 23, -23]. I'm looking for this order [50, 45, -23, 23].
so basically, if there is a tie in 23 and -23, (-)23 has the precedence, otherwise it should be desc order.

4

I don't believe this is possible even using sort script.

5

Hi @_baba.

so basically, if there is a tie in 23 and -23, (-)23 has the precedence, otherwise it should be desc order.

You can write a sort script to do this.

You'd have to pick scores such that the score for -23 is higher than 23.

How about making the score two times the absolute value of change? The scores for -23 and 23 would be 46.
Then, we can add one if change is negative. So, -23 would score 47 while 23 would still score 46.

Multiplying by two ensures adjacent numbers are at two least scores away.

# setup
PUT sort
{
  "mappings": {
    "properties": {
      "change": {
        "type": "integer"
      }
    }
  }
}

PUT sort/_bulk
{"index": {"_id":1}}
{"change":-23}
{"index": {"_id":2}}
{"change":23}
{"index": {"_id":3}}
{"change":45}
{"index": {"_id":4}}
{"change":50}

GET sort/_search
{
  "query": {
    "match_all": {}
  },
  "sort": {
    "_script": {
      "type": "number",
      "script": {
        "source": """
        if (doc['change'].empty) {
          return 0;
        }
        def change = doc['change'].value;
        if (change >= 0) {
          return change * 2;
        }
        return (change * -1 * 2) + 1;
        """
      },
      "order": "desc"
    }
  }
}

# result
{
  "took" : 7,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 4,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [
      {
        "_index" : "sort",
        "_id" : "4",
        "_score" : null,
        "_source" : {
          "change" : 50
        },
        "sort" : [
          100.0
        ]
      },
      {
        "_index" : "sort",
        "_id" : "3",
        "_score" : null,
        "_source" : {
          "change" : 45
        },
        "sort" : [
          90.0
        ]
      },
      {
        "_index" : "sort",
        "_id" : "1",
        "_score" : null,
        "_source" : {
          "change" : -23
        },
        "sort" : [
          47.0  # (23 * 2) + 1 because change is negative
        ]
      },
      {
        "_index" : "sort",
        "_id" : "2",
        "_score" : null,
        "_source" : {
          "change" : 23
        },
        "sort" : [
          46.0
        ]
      }
    ]
  }
}
2 Likes
6

Thanks for sharing this.

7

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.