ORDER BY the IN value list position

user_6223331 0
Viewed 126

I need to build elasticsearch query with sorting by field value positioning from an array.

Similar to MySQL:

SELECT * FROM `comments` ORDER BY FIELD(`id`,'17','3','5','12') DESC, id DESC;

or Postgres:

SELECT * FROM comments
LEFT JOIN unnest('{12,5,3,17}'::int[]) WITH ORDINALITY t(id, ord) USING (id) ORDER  BY t.ord, id DESC;
elasticsearch
2 Answers

You are looking for custom sorting in elasticsearch
it is possible to achieve it via painless script
here is what I do

PUT my_test
{
  "mappings": {
    "properties": {
      "animal": {
        "type": "keyword"
      }
    }
  }
}

Populate docs

POST my_test/_doc
{
  "animal": "mouse"
}
POST my_test/_doc
{
  "animal": "cat"
}
POST my_test/_doc
{
  "animal": "dog"
}

Custom sort

GET my_test/_search
{
  "query": {
    "match_all": {}
  },

    "sort": {
        "_script": {
            "type": "number",
            "script": {
                "lang": "painless",
                "source": "if(params.scores.containsKey(doc['animal'].value)) { return params.scores[doc['animal'].value];} return 100000;",
                "params": {
                    "scores": {
                        "dog": 0,
                        "cat": 1,
                        "mouse": 2
                    }
                }
            },
            "order": "asc"
        }
    }
}
user_93311340

Just to compliment the answer above.

You can combine the approach to promote some items to the top and keep the origin sorting by relevance for all other items of the search result.

"sort": {
    "_script": {
        "type": "number",
        "script": {
            "lang": "painless",
            "source": "if(params.scores.containsKey(doc['sku'].value)) { return params.scores[doc['sku'].value];} return 10",
            "params": {
                "scores": {
                    "3JK76": 0,
                    "8UF78": 1
                }
            }
        },
        "order": "asc"
    },
    "_score": { "order": "desc" }
}
user_3921180
Related