Sort script input needed

Elastic Stack Elasticsearch
1

I have a dataset like this (sometimes Source field does not exist):

Id            Source                     Sender
--------------------------------------------------------------------
1                                 test@abc.com
2                                 amy@abc.com
3           Amy's channel         amy@abc.com
4           Avery's channel       test@abc.com
5           Bill's channel        xxx@abc.com

I want to sort it by the Source field first, but if it doesn't exist, take the Sender field, the order should be 3 -> 2 -> 4 -> 5 -> 1.

Tried:

GET emailmessages-sort/_search
{
  "query": {
    "match_all": {}
  },
  "sort": {
    "_script": {
      "script": "if (doc['Source'].size() != 0) doc['Sender'].value; else doc['Sender'].value",
      "type": "string",
      "order": "asc"
    }
  }
}

But the last document is 5 (it should be 1) (because it's sorted by Sender which is xxx@test.com instead of Source which is Bill.

It's 3 -> 2 -> 4 -> 1 -> 5 and it should be 3 -> 2 -> 4 -> 5 -> 1

How can I sort by both fields combined, is this even possible?

2 
"sort": [
  {"Source": "asc"},
  {"Sender": "asc"}
]

does not work?

3

Nope, this will sort first by Source, and after that by Sender, it will be 3 -> 4 -> 5 -> 2 -> 1, which means "Source = Bill's channel" will be before "Sender = amy@abc.com"

4

How about the script below?

if (doc.containKey('Source')){doc['Source'].value}else{doc['Sender'].value}
5

This is not working because member method [java.util.Map, containKey/1] is not found, but I guess, by the logic, your answer would sort it in the same way ( 3 - 4 -5 - 2 - 1) which is not good..

6

Really? doc.containsKey also appears in the NOTE of the official document.

and by the logic this script will sort in 3, 2, 4, 5, 1.

7

Oh, sorry. there was typo.

if (doc.containsKey('Source')){doc['Source'].value}else{doc['Sender'].value}

is correct.

8

reason" : "A document doesn't have a value for a field! Use doc[].size()==0 to check if a document is missing a field!

bot fields are mapped as a keyword btw

10

Ok, I found the issue, it wasn't working because of Capitalized letters, I just put on both fields "normalizer" : "lowercase", and it works now as:

GET emailmessages-sort2/_search
{
  "query": {
    "match_all": {}
  },
  "sort": {
    "_script": {
      "script": "if (doc['Source'].size() != 0) doc['Source'].value; else doc['Sender'].value",
      "type": "string",
      "order": "asc"
    }
  }
}
1 Like
11

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