Script based conditional sorting for date fields inside nested object

Elastic Stack Elasticsearch
1

Hello.

I have the following property in my mapping:

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

attempts is an array of objects as you may see.

The functionality I need is the following:

  1. When a user sends me an attribute type_A
  2. I need to sort all my documents the following way
  3. doc.attempts.type === 'type_A' -> sort by doc.attempts.date asc | desc

So basically I need to enter the attempts array of each document, check if the attempt is of type 'type_A' and return the date, if it's not of 'type_A' I might need to ignore it somehow...not sure..

What I tried is the following:

_script: {
      nested: {
        path: 'attempts',
      },
      script: {
        lang  : 'painless',
        source: `
          if (
            doc['attempts.number'].value == params.number 
            && doc['attempts.type'].value == params.type
          ) {
            return doc['attempts.date'].value.millis
          } else {
            return 0
          }
          `,
        params: { type, number },
      },
      order : sortType,
      type  : 'number',
    }

the problem I faced is the following:

Imagine I have 10 documents, each of which have attempts array with 10 elements inside. Each of these 10 elements have type 'type_A' and their date are like these: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

When I do sorting 'type_A asc', with size: 5, it returns me the docs with these attempts dates: 6, 7, 8, 9, 10

As I understand from the output, attempts with date from 1 to 5 returned their dates. The rest returned 0, so since I requested sorting by asc, elastic returned 5 elements with lowest dates, in my case it's 0.

What I need is to return the date when attempt has the required type and return something other for the attempts with other types, that would work for asc and desc sorting.

Can anyone help, please?

2

Though I don't understand all of your posts, it seems not to need painless script.

Nested query with inner hits with size and sort is not enough?

3

thank you for the response.
I do not need a nested query which would return documents matching nested criteria.
I need to get all documents, which would be sorted by nested fields as I described in the initial message. Is that possible with inner hits?

4

Document A:
{"attempts": [{"type":type_A, "date":2}, {"type": type_A, "date": 3},{"type": type_B,"date":1}]}
Document B:
{"attempts": [{"type":type_A, "date":1}, {"type": type_A, "date": 5},{"type": type_B,"date":1}]}

What kind of result do you want by 'type_A asc'? Which document comes first?

What do you mean by size here? Size for documents or size for nested objects??

5

realist structure in my case would be the following. Attempts can have up to 3 attempts of the same type, I distinguish them by number field.
Document A:
{
"attempts": [
{"type":type_A, "date":2, "number": 1},
{"type": type_A, "date": 3, "number": 2}
{"type": type_B,"date":1, "number": 1}
]
}
Document B:
{
"attempts": [
{"type":type_A, "date":1, "number": 1},
{"type": type_A, "date": 5, "number": 2},
{"type": type_B,"date":1, "number": 1}
]
}

I can't just sort by type_A asc as I also need to pass the number somehow, this is what I tried to do in my script.
So with sorting by attempts with type_A asc and number 2 I would expect to receive the following result:

  1. Document A
  2. Document B

What do you mean by size here? Size for documents or size for nested objects??
I mean size of documents, like a limit in sql

6

I see. So, you (or your user) can identify a single nested object in a document by type and number in the "query"

This is an example for "type: A, number:1, order:desc".

GET test_script_sort_on_nested/_search
{
  "query":{
    "bool": {
      "minimum_should_match": 1,
      "should":[
        {
          "constant_score": {
            "filter": {"match_all":{}},
            "boost": 0
          }
        },
        {
          "nested": {
            "path": "attempts",
            "query": {
              "script_score":{
                "query":{
                  "bool":{
                    "filter":[
                      {"term": {"attempts.type": "A"}},
                      {"term": {"attempts.number": 1}}]
                  }
                },
                "script":{
                  "source": "doc['attempts.date'].value.getMillis()"
                }
              }
            }
          }
        }]
    }
  }
}
7

Oh, I may have figured out what you wanted to do.

Do you mean "I want some documents which meets some conditions to appear at the end, whether sorted by 'asc' or 'desc'"?

If so, it is not possible. Sorting values should be number or string. "asc" is just the opposite of "desc". No special value such as null is allowed.

8

Imagine a table on ui, which have columns:

  • createdDate
  • attemptOne
  • attemptTwo
  • attemptThree

There is pagination on this table.

User is currently on a page 2. And he sees the following documents (sorted by createdDate by default):

  • Doc_A
  • Doc_B
  • Doc_C
  • Doc_D

And now user decides that he wants to sort all these documents by column attemptTwo which under hood should sort documents by attempts.date of documents that have number === 2

As I understand from your example of query with nested filtering, it would match only those documents that have type and `number mentioned in criteria and would display them to ui.

If that is the case, this is not what I need because I'm not trying to change the list of documents that were show on a page 2 before this sorting, I just want to sort them.

Update. I have just tested your query and it seems like it's working as expected, though I do not understand why. Why is it considered as desc sorting, how to make it asc?

9

I have no idea about re-sorting only documents on page 2 with elasticsearch. I suppose it is not supported. You need RANK function of SQL, which is not supported in elasticsearch.

IMHO, as the client already get the information of page2, it is better to be sorted on the client side. Such local handling of data is not a job that should be handled by elasticsearch. Elasticsearch is for global sorting.

That is because default order for score is descending and the script_score gives more score for later date. Documents with later date comes first.

For ascending, try below:

                "script":{
                  "source": "10000000000000 - doc['attempts.date'].value.getMillis()"
                }
10

I had to go with another approach which is not a script but sort with nested filtering. I wasn't aware of this at the moment of opening this ticket but your responses pushed me in the right direction, so thank you for your help!

11

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.