How to sort multiple fields with exception condition

Question

I cannot use FieldSortBuilder to sort by multiple fields and do the exception condition. So I am wondering can we use painless to do it?

Below is data model.

{ "name" : "string",
  "email" : "string",
  "schoolNumber" : "number",
  "startTime" : "number",
  "endTime" : "number",
}

Examples:

{ "name" : "Micheal",
  "email" : "mi@gmail.com",
  "schoolNumber": 1,
  "startTime" : 123,
}

{ "name" : "Racheal",
  "email" : "ra@gmail.com",
  "schoolNumber": 1,
  "startTime" : 456,
  "endTime" : 789,
}

{ "name" : "Racheal",
  "email" : "ra@gmail.com",
  "schoolNumber": 2,
  "startTime" : 987,
}

Only endTime is optional. I want to sort all the profiles in startTime by asc, these profiles only have startTime, endTime should inactive and they should have the same schoolNumber.

public static final String SORT_SCRIPT =
      "if (doc['endTime.raw'].value == null) { 
           //sort by startTime which have same schoolNumber
       } else { 
           //sort by endTime which have same schoolNumber
       }";

Another solution: I want to use FieldSortBuilder to set nestedFiler to exclude the endTime

sortBuilder.setNestedFilter(new TermQueryBuilder(LICENSE_ASSIGNMENT_ACTIVATION_TIME_PROPERTY, value:???)

But I don't know how to set value. I have tried with "null" "false" "ScoreMode.None". All failed.

`nestedFilter` is meant for nested documents which in elasticsearch is based on this link https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html. Could you share the mapping details? Also do you mean that you only want to sort the documents based on `startTime` and that you'd want the Java API code to do that? — Kamal Kunjapur, Apr 16 '19 at 12:17
The condition which you've mentioned `if(this) sort by startTime (else) sort by endTime` is only possible at client side once you have all the documents available in your java code from ES. If you sort on mutliple fields, then the way it should work is first sort by `startTime` and then again sort the result of that using `endTime`. — Kamal Kunjapur, Apr 16 '19 at 12:23
If you mention condition as mentioned in ? like that in painless script, which works on ES, you should know that sorting must work on only on one value/field at a time, having condition like that would mean, to have painless consider either `startTime` or `endTime` during sorting for **that document**. Which means it would consider value of `startTime` for `document 1` and would consider value of `endTime` for `document 2`. And then perform sort on mix of values from `startTime` and `endTime`, which won't be right. That's why it must be performed on client side once you have all the documents. — Kamal Kunjapur, Apr 16 '19 at 12:25
Thanks a lot, Kamal. Let me simplify my question, I want to sort by startTime, and only the documents which not have endTime. Is that possilbe to use painless or FieldSortBuilder to implement? @Kamal — Lena, Apr 16 '19 at 17:26
sure that's possible. I will work on it and update you in a while :) — Kamal Kunjapur, Apr 16 '19 at 17:55

score 0 · Answer 1 · answered Apr 16 '19 at 21:09

Here is what I've come up with. First let me share you the Elasticsearch query as how this can be done.

Note that I've made use of Exists Query.

Elasticsearch DSL Request

POST <your_index_name>/_search
{
  "query": {
    "bool": {
      "must_not": [
        {
          "exists": {
            "field": "endTime"
          }
        }
      ]
    }
  },
  "sort": [
    {
      "startTime": {
        "order": "desc"
      }
    }
  ]
}

What I've done is simply executed the filter condition on the query level which means using the above query, I only end up with documents without endTime and on these documents am actually executing sorting operation on startTime field.

Below is how its done via Java API.

Java API:

SearchRequest searchRequest = new SearchRequest("<your_index_name>");
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
//Exists Query 
searchSourceBuilder.query(QueryBuilders.boolQuery().mustNot(QueryBuilders.existsQuery("endTime")));

//Sort Logic    
FieldSortBuilder mySort = SortBuilders.fieldSort("startTime").order(SortOrder.DESC);
searchSourceBuilder.sort(mySort);
searchRequest.source(searchSourceBuilder);

Let me know if this is what you are looking for!

Thanks for the solution. I tried it, but it does not work, the result is still just sort by startTime with all profiles. Because for elastic search, the query and sort execute in parallel, so we can query profiles only have startTime. But when we sorting, the query condition does not work, it will sort all profiles with startTime. @Kamal — Lena, Apr 18 '19 at 00:33
That's not correct. Sort will only happen post query execution. — Kamal Kunjapur, Apr 18 '19 at 00:59
Could you share me your mapping details. The query does exactly what you requested on a sample index I've created with the above documents as in. I can check your mapping and let you know more on this. — Kamal Kunjapur, Apr 18 '19 at 01:00

How to sort multiple fields with exception condition

1 Answers1

Elasticsearch DSL Request

Java API: