Order dificulities for exact match

Elastic Stack Kibana
1

Hi, I used exact match for a list of numbers. the output order is not the same as input. I tried multiple methods but there some issues.
mydb is my database, the input is an array of integers to be matched to "my_id" and the output is limited to two fields.

method1:

GET mydb/_search
{
  "query": {
    "terms": {
      "my_id": [1010, 203, 456, 789]
    }
  },
  "script_fields": {
    "order_test": {
      "script": {
        "lang": "painless",
        "source": """
          Map orderMap = new HashMap();
          orderMap.put(1010, 0);
          orderMap.put(203, 1);
          orderMap.put(456, 2);
          orderMap.put(789, 3);
          return orderMap.getOrDefault(doc['my_id'].value, 9999);
        """
      }
    }
  },
  "_source": ["insdc_accession_id","my_id"] 
}

Method2:

GET mydb/_search
{
  "query": {
    "terms": {
      "my_id": [1010, 203, 456, 789] 
    }
  },
  "sort": [
    {
      "_script": {
        "type": "number",
        "script": {
          "lang": "painless",
          "source": """
            def order = [1010, 203, 456, 789]; 
            return order.indexOf(doc['my_id'].value);
          """
        },
        "order": "asc"
      }
    }
  ],
  "_source": ["insdc_accession_id","my_id"] 
}

Method3:

GET mydb/_search
{
  "query": {
    "terms": {
      "my_id": [1010, 203, 456, 789]  
    }
  },
  "sort": [
    {
      "_script": {
        "type": "number",
        "script": {
          "lang": "painless",
          "source": """
            long v = doc['my_id'].value;
            if (v == 1010L) { return 0; }
            else if (v == 203L) { return 1; }
            else if (v == 456L) { return 2; }
            else if (v == 789L) { return 3; }
            else { return 9999; }
          """
        },
        "order": "asc"
      }
    }
  ],
  "_source": ["insdc_accession_id","my_id"] 
  }

The expected result should be [1010, 203, 456, 789] for my_id,
the result of the first two methods are the same as [203, 456, 789, 1010],
the third method is functional but not easy to implement for a high number of values.
I am looking forward the simplest way to keep the order as it is received.
Thank you

2

Hello @shrm, may I ask you what's the reason behind using HashMap? This is a data structure that doesn't guarantee the order of elements.

3

Hi @piotrprz
I tried that to store the mapping between my_id values and their order in the list, allowing me to use that order for sorting in your query. But seems it was a mistake :smiley:

4

Who not just make separate queries in the sequence you desire ?

To my understanding, ES does not directly support what you want, which seems to be "find docs that match any of x1,x2,x3, and return those docs in order doc-with-x1, doc-with-x2, doc-with-x3", in the most general/scaleable way.

Perhaps there is, and it's in my blind spot.

So maybe there are clever / hacky ways to achieve your goal, but I dont see why these are better than the KISS solution?

We have only tiny window into what you are trying to do here, perhaps if you expanded a bit on the wider picture, other ideas might emerge?

5

Sorting in the query is possible in ES, but maybe I missed your use case. I'm not aware of making "own sorting algorithm", but sorting ASC and DESC is for example possible in ES|QL too.

6

But he’s exactly asking to sort in the order his query terms are given. It’s not clear why, obviously one can sort responses in code/application yourself.

query on fruit being apple or orange or banana

results sorted on match on apple first, match on orange second and match on banana third.

All inside the same query and (I presume) scalable beyond 3 or 4 terms. I’m also assuming there’s one and exactly one doc match per term, though that’s also not specified.