ElasticSearch script sorting by date field inside nested object-CodePudding

I have the following field in my mapping

"attempts": {
  "type": "nested",
  "properties": {
    "type": {
      "type": "keyword"
    },
    "reason": {
      "type": "keyword"
    },
    "status": {
      "type": "keyword"
    },
    "number": {
      "type": "integer"
    },
    "date": {
      "type": "date"
    }
  }
}

I need to do the following:

when a certain type and number is provided from ui, I have to sort by the field attempts.date

I suppose it can be done with the script but I can't make it work.

This is what I tried:

    sort: [
    {
    _script: {
      nested: {
        path: 'attempts',
      },
      script: {
        lang  : 'painless',
        source: `if (
          doc['attempts.number'].value === params.number 
          && doc['attempts.type'].value === params.attemptType
          ) {
          return doc['attempts.date'].date.getMillis()
        }`,
        params: { attemptType, number },
      },
      order : sortType,
      type  : 'number',
    },
  },
  {
    _id: 'desc'
  }
]

It does not give any error, it just returns the records in different order every time.

Can anybody help?

CodePudding user response：

I don't know in which language you are implementing it but I would do it this way (if I understand your problem correctly).

The intention is to leave the rule of which short inside your application not in the query. Your application will inject the sort you want in the query.

From your mapping I indexed two documents:

POST idx_test/_doc
{
  "attempts": {
    "type": "type_a",
    "number": 10,
    "date": "2020-07-21"
  }
}

POST idx_test/_doc
{
  "attempts": {
    "type": "type_a",
    "number": 10,
    "date": "2020-07-20"
  }
}

When your front sends the type and number you will have a kind of factory that will return the sort you want.

Front sends type=type_a and number=10. Factory will return sort:

  "sort": [
    {
      "attempts.date": {
        "order": "asc",
        "nested_path": "attempts"
      }
    }
  ]

If you do not receive the expected type and number you can have a default sort or not inject the sort in the query.

In the end your query with sort would look like this:

GET idx_test/_search
{
  "query": {
    "match_all": {}
  }, 
  "sort": [
    {
      "attempts.date": {
        "order": "asc",
        "nested_path": "attempts"
      }
    }
  ]
}

With this query you will have the sort by the date field. It's good for you to have a tiebreaker if the dates are the same, I suggest using the score or an id that you define for the document.