Sort on two fields

Elastic Stack Elasticsearch
1

Hello,

this only sort on a field string. I would like to sort on two fields, the first is _score and then a string field, e.g. cityname.

searchRequestBuilder.setTrackScores(true);
searchRequestBuilder.addSort(SortBuilders.fieldSort("_score").order(SortOrder.DESC));
searchRequestBuilder.addSort(SortBuilders.fieldSort("cityname").order(SortOrder.DESC).missing("_last");

the sorted result on _score is perfect, however, on the cityname, there are some empty field such as the one below.

kuala lumpur  80
              75
new york      65

any idea how to make it to

kuala lumpur  80
new york      65
              75

thank you.

jason

2

The response is valid.

I'd say the problem is in your data and that there are a number of records that have the cityname as an empty value, you should really check your data quality first.

3

hey warkolm, thanks for the response. well, cityname is just an example, another example would probably be chair, some chair has the arm rest and some don't but there are some chair with very high score. so we want highest score with chair with arm rest sorted to the top.

we thought of a workaround like search on exists field and sort by score or boost negatively on the field that missing so score would be lower.

4

Hmm now you mention it, I don't know how the field would be there like that.

I'll leave this to someone else cause I'm going to bed :stuck_out_tongue:

1 Like
5

I haven't tried this but I imagine you could do this with a combination of script sort and field sort. So you would have a script which would return a high number like 10000 if the cityname is present and 0 if not. And then chain that with the field sorts for _score and cityname.

searchRequestBuilder.setTrackScores(true);
searchRequestBuilder.addSort(SortBuilders.scriptSort(YOUR_SCRIPT).order(SortOrder.DESC));
searchRequestBuilder.addSort(SortBuilders.fieldSort("_score").order(SortOrder.DESC));
searchRequestBuilder.addSort(SortBuilders.fieldSort("cityname").order(SortOrder.DESC).missing("_last");
1 Like
6

hi colin, thanks for your response. on the code 'YOUR_SCRIPT', could you give some sample? noob here to code script. could you also give url script documentation how to do that?

7

Information on script based sorting can be found here: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-sort.html#_script_based_sorting

Information on scripting in general can be found here: https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting.html

JavaDoc (and source) for ScriptSortBuilder in the Java API can be found here: https://github.com/elastic/elasticsearch/blob/1.x/src/main/java/org/elasticsearch/search/sort/ScriptSortBuilder.java

The script score (in json but can easily be converted to JAVA API) would be something like:

"_script" : {
    "script" : "doc[field].value ? factor : 0",
    "type" : "number",
    "params" : {
        "factor" : 10000
        "field" : "cityname"
    },
    "order" : "desc"
}

I'd recommend that you set the language to lucene expressions (see scripting documentation above) to bypass the security concerns around dynamic scripting with groovy (explained at the top of the scripting documentation in the link above). You may also consider storing the script in a file or indexed script.

1 Like
8

hey colin,

thanks for great help, unfortunately we have disabled dynamic scripting.

String script = "doc[field].value ? factor : 0";
searchRequestBuilder.addSort(SortBuilders.scriptSort(script, "number").order(SortOrder.DESC).param("factor", 10000).param("field", "cityname"));


nested: ScriptException[dynamic scripting disabled]; }
	at org.elasticsearch.action.search.type.TransportSearchTypeAction$BaseAsyncAction.onFirstPhaseResult(TransportSearchTypeAction.java:272)
	at org.elasticsearch.action.search.type.TransportSearchTypeAction$BaseAsyncAction$3.onFailure(TransportSearchTypeAction.java:224)
	at org.elasticsearch.search.action.SearchServiceTransportAction$3.handleException(SearchServiceTransportAction.java:188)
9

As I mentioned above, you should set the language for the script to use the lucene expressions language which does not suffer from the same security concerns as the other scripting languages as it is very restrictive in functionality and so works when dynamic scripting is disabled.

String script = "doc[field].value ? factor : 0";
searchRequestBuilder.addSort(SortBuilders.scriptSort(script, "number").lang("expressions").order(SortOrder.DESC).param("factor", 10000).param("field", "cityname"));

You could also save the script as a file (e.g. my_script.groovy) in the $ES_CONF_DIR/scripts folder and then reference the file script in the script sort:

String script = "my_script";
searchRequestBuilder.addSort(SortBuilders.scriptSort(script, "number").order(SortOrder.DESC).param("factor", 10000).param("field", "cityname"));

or you could do both :slight_smile:

1 Like