# Sorting by \_score on an aggregation
**URL:** https://discuss.elastic.co/t/sorting-by-score-on-an-aggregation/224728
**Category:** Elasticsearch
**Created:** [March 23, 2020, 8:26pm UTC](https://discuss.elastic.co/t/sorting-by-score-on-an-aggregation/224728 "2020-03-23T20:26:10Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Tim\_Estes](https://discuss.elastic.co/u/Tim_Estes)
#### Post date: [March 23, 2020, 8:26pm UTC](https://discuss.elastic.co/t/sorting-by-score-on-an-aggregation/224728/1 "2020-03-23T20:26:10Z")
Hello, I have the following schema for an index, with some example documents. My use-case is that I'm indexing a book and I am indexing by page. I'm trying to make a query against the contents of each page and return the most relevant page from top N most relevant books, but I'm having trouble returning them by their \_score field in the proper order:
```auto
PUT my-index
{}
PUT my-index/_mapping
{
"properties": {
"page": {
"type": "long"
},
"title": {
"type": "keyword"
},
"content": {
"type": "text"
}
}
}
POST _bulk
{ "index" : { "_index" : "my-index", "_id" : "doc_1.pg_1" } }
{ "page" : 1, "title": "doc_1", "content": "a quick brown fox jumped over the lazy dog ..."}
{ "index" : { "_index" : "my-index", "_id" : "doc_1.pg_2" } }
{ "page" : 2, "title": "doc_1", "content": "... and the fox landed in a briar patch"}
{ "index" : { "_index" : "my-index", "_id" : "doc_2.pg_1" } }
{ "page" : 1, "title": "doc_2", "content": "a slow orange fox leapt over the barky dog ..."}
{ "index" : { "_index" : "my-index", "_id" : "doc_2.pg_2" } }
{ "page" : 2, "title": "doc_2", "content": "... and the dog lept and got the fox"}
```
So far I've created the following query aggregation to get the most relevant page of the each doc in the corpus:
```auto
GET my-index/_search
{
"size":0,
"query" : {
"simple_query_string": {
"query": "the",
"fields": ["content"]
}
},
"aggs": {
"by_title" : {
"terms" : {
"field" : "title"
},
"aggs": {
"get_top_scoring_page": {
"top_hits": {
"sort": {"_score": {"order": "desc"}},
"size": 1,
"highlight": {"fields": {"content": {}}}
}
}
}
}
}
}
```
And here's what it returns:
```auto
{
"took" : 3,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 4,
"relation" : "eq"
},
"max_score" : null,
"hits" : []
},
"aggregations" : {
"by_title" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "doc_1",
"doc_count" : 2,
"get_top_scoring_page" : {
"hits" : {
"total" : {
"value" : 2,
"relation" : "eq"
},
"max_score" : 0.10795845,
"hits" : [
{
"_index" : "my-index",
"_type" : "_doc",
"_id" : "doc_1.pg_2",
"_score" : 0.10795845,
"_source" : {
"page" : 2,
"title" : "doc_1",
"content" : "... and the fox landed in a briar patch"
},
"highlight" : {
"content" : [
"... and the fox landed in a briar patch"
]
}
}
]
}
}
},
{
"key" : "doc_2",
"doc_count" : 2,
"get_top_scoring_page" : {
"hits" : {
"total" : {
"value" : 2,
"relation" : "eq"
},
"max_score" : 0.14730778,
"hits" : [
{
"_index" : "my-index",
"_type" : "_doc",
"_id" : "doc_2.pg_2",
"_score" : 0.14730778,
"_source" : {
"page" : 2,
"title" : "doc_2",
"content" : "... and the dog lept and got the fox"
},
"highlight" : {
"content" : [
"... and the dog lept and got the fox"
]
}
}
]
}
}
}
]
}
}
}
```
Notice that the higher scoring document `doc_2` is returned _after_ `doc_1`, which is not what I want. I want to sort these results by the `_score` field in descending order, with the highest scoring title being returned first.
Can anyone help me out here? I feel like I'm very close to the answer.
---
### Author:  [@Tim\_Estes](https://discuss.elastic.co/u/Tim_Estes)
#### Post date: [March 25, 2020, 4:54pm UTC](https://discuss.elastic.co/t/sorting-by-score-on-an-aggregation/224728/2 "2020-03-25T16:54:49Z")
Ok, I found the answer, thanks to [this elastic blog](https://www.elastic.co/blog/top-hits-aggregation) . Here's the query I used:
```auto
GET my-index/_search
{
"size" : 0,
"track_total_hits": false,
"query": {
"simple_query_string": {
"query": "the",
"fields": ["content"]
}
},
"aggs": {
"top-ten-books": {
"terms": {
"field": "title",
"size": 10,
"order": {
"max_score": "desc"
}
},
"aggs": {
"top-page": {
"top_hits": {
"size" : 1,
"highlight": {"fields": {"content": {}}}
}
},
"max_score" : {
"max": {
"script": "_score"
}
}
}
}
}
}
```
And here's the answer: this time with a properly sorted return!
```auto
{
"took" : 4,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"max_score" : null,
"hits" : []
},
"aggregations" : {
"top-ten-books" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "doc_2",
"doc_count" : 2,
"max_score" : {
"value" : 0.14730778336524963
},
"top-page" : {
"hits" : {
"total" : {
"value" : 2,
"relation" : "eq"
},
"max_score" : 0.14730778,
"hits" : [
{
"_index" : "my-index",
"_type" : "_doc",
"_id" : "doc_2.pg_2",
"_score" : 0.14730778,
"_source" : {
"page" : 2,
"title" : "doc_2",
"content" : "... and the dog lept and got the fox"
},
"highlight" : {
"content" : [
"... and the dog lept and got the fox"
]
}
}
]
}
}
},
{
"key" : "doc_1",
"doc_count" : 2,
"max_score" : {
"value" : 0.10795845091342926
},
"top-page" : {
"hits" : {
"total" : {
"value" : 2,
"relation" : "eq"
},
"max_score" : 0.10795845,
"hits" : [
{
"_index" : "my-index",
"_type" : "_doc",
"_id" : "doc_1.pg_2",
"_score" : 0.10795845,
"_source" : {
"page" : 2,
"title" : "doc_1",
"content" : "... and the fox landed in a briar patch"
},
"highlight" : {
"content" : [
"... and the fox landed in a briar patch"
]
}
}
]
}
}
}
]
}
}
}
```
---
### Author:  [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)
#### Post date: [March 25, 2020, 5:30pm UTC](https://discuss.elastic.co/t/sorting-by-score-on-an-aggregation/224728/3 "2020-03-25T17:30:48Z")
I suspect you'll find [field collapsing](https://www.elastic.co/guide/en/elasticsearch/reference/7.6/search-request-body.html#request-body-search-collapse) is a simpler solution
---
### Author:  [@Tim\_Estes](https://discuss.elastic.co/u/Tim_Estes)
#### Post date: [April 7, 2020, 6:05pm UTC](https://discuss.elastic.co/t/sorting-by-score-on-an-aggregation/224728/4 "2020-04-07T18:05:57Z")
Mark\_Harwood was right. Field collapsing was a much more elegant solution. This code also solves my problem:
```auto
GET my-index/_search
{
"query": {"simple_query_string": {"query": "the", "fields": ["content"]}},
"from": 0,
"size": 10,
"collapse" : {"field" : "title"},
"sort": [{"_score": {"order": "desc"}}],
"highlight": {"fields": {"content": {}}}
}
```
Here's the response:
```auto
{
"took" : 848,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 4,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "my-index",
"_type" : "_doc",
"_id" : "doc_2.pg_2",
"_score" : 0.14730778,
"_source" : {
"page" : 2,
"title" : "doc_2",
"content" : "... and the dog lept and got the fox"
},
"fields" : {
"title" : [
"doc_2"
]
},
"highlight" : {
"content" : [
"... and the dog lept and got the fox"
]
}
},
{
"_index" : "my-index",
"_type" : "_doc",
"_id" : "doc_1.pg_2",
"_score" : 0.10795845,
"_source" : {
"page" : 2,
"title" : "doc_1",
"content" : "... and the fox landed in a briar patch"
},
"fields" : {
"title" : [
"doc_1"
]
},
"highlight" : {
"content" : [
"... and the fox landed in a briar patch"
]
}
}
]
}
}
```
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [May 5, 2020, 6:06pm UTC](https://discuss.elastic.co/t/sorting-by-score-on-an-aggregation/224728/5 "2020-05-05T18:06:01Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.