ElasticSearch script sorting by date field inside nested object

Question

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?

Why you don't leave this rule in your application? That way you can create a logic that returns different sorts and the query would have the simplest sort criterion. — rabbitbr
– rabbitbr, Commented Jul 21, 2022 at 1:03
attempts is an array which have objects similar in basically everything except the type, how do you suggest to to build the simplest sort criterion if I do it the way you suggest? Please share a working example if you can because I can't get it. — Shambala
– Shambala, Commented Jul 21, 2022 at 5:48

rabbitbr · Accepted Answer · 2022-07-21 16:50:29Z

1

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.

answered Jul 21, 2022 at 16:50

rabbitbr

3,2812 gold badges8 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shambala Over a year ago

thank you for guiding me in this direction. I partially implemented your suggestion. Partially because I have also added filter into the sort query and it works fine. Thanks again

Collectives™ on Stack Overflow

ElasticSearch script sorting by date field inside nested object

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related