# Sort on two fields **URL:** \ **Category:** Elasticsearch\ **Created:** [June 1, 2015, 4:31am UTC](https://discuss.elastic.co/t/sort-on-two-fields/1608 "2015-06-01T04:31:46Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Jason\_Wee](https://avatars.discourse-cdn.com/v4/letter/j/7ea924/32.png) [@Jason\_Wee](https://discuss.elastic.co/u/Jason_Wee)\ **Post date:** [June 1, 2015, 4:31am UTC](https://discuss.elastic.co/t/sort-on-two-fields/1608/1 "2015-06-01T04:31:46Z")
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 ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [June 2, 2015, 11:35am UTC](https://discuss.elastic.co/t/sort-on-two-fields/1608/2 "2015-06-02T11:35:32Z")
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. ---
**Author:** ![Jason\_Wee](https://avatars.discourse-cdn.com/v4/letter/j/7ea924/32.png) [@Jason\_Wee](https://discuss.elastic.co/u/Jason_Wee)\ **Post date:** [June 2, 2015, 11:47am UTC](https://discuss.elastic.co/t/sort-on-two-fields/1608/3 "2015-06-02T11:47:52Z")
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. ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [June 2, 2015, 11:50am UTC](https://discuss.elastic.co/t/sort-on-two-fields/1608/4 "2015-06-02T11:50:33Z")
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 😛 ---
**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\ **Post date:** [June 2, 2015, 12:25pm UTC](https://discuss.elastic.co/t/sort-on-two-fields/1608/5 "2015-06-02T12:25:07Z")
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`. ```auto 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"); ``` ---
**Author:** ![Jason\_Wee](https://avatars.discourse-cdn.com/v4/letter/j/7ea924/32.png) [@Jason\_Wee](https://discuss.elastic.co/u/Jason_Wee)\ **Post date:** [June 2, 2015, 12:36pm UTC](https://discuss.elastic.co/t/sort-on-two-fields/1608/6 "2015-06-02T12:36:16Z")
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? ---
**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\ **Post date:** [June 2, 2015, 1:52pm UTC](https://discuss.elastic.co/t/sort-on-two-fields/1608/7 "2015-06-02T13:52:29Z")
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](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](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](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: ```json "_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. ---
**Author:** ![Jason\_Wee](https://avatars.discourse-cdn.com/v4/letter/j/7ea924/32.png) [@Jason\_Wee](https://discuss.elastic.co/u/Jason_Wee)\ **Post date:** [June 3, 2015, 5:59am UTC](https://discuss.elastic.co/t/sort-on-two-fields/1608/8 "2015-06-03T05:59:32Z")
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) ``` ---
**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\ **Post date:** [June 3, 2015, 8:21am UTC](https://discuss.elastic.co/t/sort-on-two-fields/1608/9 "2015-06-03T08:21:09Z")
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. ```java 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: ```java String script = "my_script"; searchRequestBuilder.addSort(SortBuilders.scriptSort(script, "number").order(SortOrder.DESC).param("factor", 10000).param("field", "cityname")); ``` or you could do both 🙂 ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 12:10am UTC](https://discuss.elastic.co/t/sort-on-two-fields/1608/10 "2017-07-06T00:10:02Z")