Is it possible to sort by substring of value in Elasticsearch (Opensearch)?

Question

Let's say I have these documents:

[
  {
    "_index" : "index_name",
    "_type" : "_doc",
    "_id" : "some_obj.{RANDOM UUID}:{REAL ID}",
    "_source" : {
      "id" : "some_obj.{RANDOM UUID}:{REAL ID}",
      "parent_id": "{SOME ID}"
      "name" : "{some name}"
    }
  },
  { ... }
]

I trying to get these objects by using query:

GET index_name/_search
{
  "sort" : [
    { "id": {"order": "asc"}}
  ],
  "query": {
    "terms": {
      "parent_id": [ "1" ]
    }
  }
}

Because of {RANDOM UUID} in the middle of id field I can't order by {REAL ID}

Is it possible to remove some_obj.{RANDOM UUID}: part for sorting or split it by : and use second part for sorting or some another way to be able sort by {REAL ID}?

Answer 1

Following query should do it. I assumed id field in your mapping is keyword and {REAL ID}is numeric. If it is a string, you should change type in the query to string and remove Long.parse from the return statement.

{
  "sort": [
    {
      "_script": {
        "type": "number",
        "script": {
          "lang": "painless",
          "source": """
            return Long.parseLong(doc['id'].value.splitOnToken(':')[1]);
          """
        },
        "order": "asc"
      }
    }
  ],
  "query": {
    "term": {
      "parent_id": {
        "value": "1"
      }
    }
  }
}