# Script based conditional sorting for date fields inside nested object **URL:** \ **Category:** Elasticsearch\ **Tags:** painless\ **Created:** [July 22, 2022, 10:06pm UTC](https://discuss.elastic.co/t/script-based-conditional-sorting-for-date-fields-inside-nested-object/310432 "2022-07-22T22:06:33Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![M\_L](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/m_l/32/99690_2.png) [@M\_L](https://discuss.elastic.co/u/M_L)\ **Post date:** [July 22, 2022, 10:06pm UTC](https://discuss.elastic.co/t/script-based-conditional-sorting-for-date-fields-inside-nested-object/310432/1 "2022-07-22T22:06:33Z")
Hello. I have the following property in my mapping: ```auto "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: ```auto _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? ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [July 23, 2022, 11:44am UTC](https://discuss.elastic.co/t/script-based-conditional-sorting-for-date-fields-inside-nested-object/310432/2 "2022-07-23T11:44:49Z")
Though I don't understand all of your posts, it seems not to need painless script. Nested query with [inner hits](https://www.elastic.co/guide/en/elasticsearch/reference/current/inner-hits.html) with `size` and `sort` is not enough? ---
**Author:** ![M\_L](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/m_l/32/99690_2.png) [@M\_L](https://discuss.elastic.co/u/M_L)\ **Post date:** [July 23, 2022, 1:15pm UTC](https://discuss.elastic.co/t/script-based-conditional-sorting-for-date-fields-inside-nested-object/310432/3 "2022-07-23T13:15:58Z")
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? ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [July 23, 2022, 1:40pm UTC](https://discuss.elastic.co/t/script-based-conditional-sorting-for-date-fields-inside-nested-object/310432/4 "2022-07-23T13:40:45Z")
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? > [@M\_L](#): > > When I do sorting 'type\_A asc', with size: 5, it returns me the docs with these attempts dates: 6, 7, 8, 9, 10 What do you mean by `size` here? Size for documents or size for nested objects?? ---
**Author:** ![M\_L](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/m_l/32/99690_2.png) [@M\_L](https://discuss.elastic.co/u/M_L)\ **Post date:** [July 23, 2022, 2:01pm UTC](https://discuss.elastic.co/t/script-based-conditional-sorting-for-date-fields-inside-nested-object/310432/5 "2022-07-23T14:01:00Z")
> [@Tomo\_M](#): > > 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}]} 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 ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [July 23, 2022, 2:04pm UTC](https://discuss.elastic.co/t/script-based-conditional-sorting-for-date-fields-inside-nested-object/310432/6 "2022-07-23T14:04:59Z")
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". ```auto 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()" } } } } }] } } } ``` ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [July 23, 2022, 3:06pm UTC](https://discuss.elastic.co/t/script-based-conditional-sorting-for-date-fields-inside-nested-object/310432/7 "2022-07-23T15:06:48Z")
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. ---
**Author:** ![M\_L](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/m_l/32/99690_2.png) [@M\_L](https://discuss.elastic.co/u/M_L)\ **Post date:** [July 23, 2022, 8:59pm UTC](https://discuss.elastic.co/t/script-based-conditional-sorting-for-date-fields-inside-nested-object/310432/8 "2022-07-23T20:59:49Z")
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? ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [July 24, 2022, 2:09am UTC](https://discuss.elastic.co/t/script-based-conditional-sorting-for-date-fields-inside-nested-object/310432/9 "2022-07-24T02:09:44Z")
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. > [@M\_L](#): > > 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? 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: ```auto "script":{ "source": "10000000000000 - doc['attempts.date'].value.getMillis()" } ``` ---
**Author:** ![M\_L](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/m_l/32/99690_2.png) [@M\_L](https://discuss.elastic.co/u/M_L)\ **Post date:** [July 27, 2022, 12:00pm UTC](https://discuss.elastic.co/t/script-based-conditional-sorting-for-date-fields-inside-nested-object/310432/10 "2022-07-27T12:00:42Z")
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! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [August 24, 2022, 12:01pm UTC](https://discuss.elastic.co/t/script-based-conditional-sorting-for-date-fields-inside-nested-object/310432/11 "2022-08-24T12:01:09Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.