# I cannot sort the aggregation result by the values of the grouped set

**URL:** <https://discuss.elastic.co/t/i-cannot-sort-the-aggregation-result-by-the-values-of-the-grouped-set/320121>\
**Category:** Elasticsearch\
**Created:** [November 30, 2022, 8:21am UTC](https://discuss.elastic.co/t/i-cannot-sort-the-aggregation-result-by-the-values-of-the-grouped-set/320121 "2022-11-30T08:21:19Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Emanuel\_Zienecker](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emanuel_zienecker/32/77580_2.png) [@Emanuel\_Zienecker](https://discuss.elastic.co/u/Emanuel_Zienecker)\
**Post date:** [November 30, 2022, 8:21am UTC](https://discuss.elastic.co/t/i-cannot-sort-the-aggregation-result-by-the-values-of-the-grouped-set/320121/1 "2022-11-30T08:21:19Z")

</div>

Example: I have a parent/child relation. The parent are devices, the children are data points. The data points have e.g. temperature values. Now I want to make an aggregation in which I determine the maximum temperature of the device, in addition group by device.

Previously, the data points were stored in the asset document (nested). There I had the following json which has delivered the desired result:

```auto
{
  "size": 0,
  "query": {
    "match_all": {
      "boost": 1.0
    }
  },
  "aggregations": {
    "group": {
      "terms": {
        "field": "group.keyword",
        "size": 20,
        "min_doc_count": 1,
        "shard_min_doc_count": 0,
        "show_term_doc_count_error": false,
        "order": [
          {
            "max_temp": "asc"
          },
          {
            "_key": "asc"
          }
        ]
      },
      "aggregations": {
        "max_temp": {
          "max": {
            "field": "dp.temp"
          }
        }
      }
    }
  }
}

```

With this I got the following result (simplified):

1. g2 -\> 2.2
2. g1 -\> 5.7
3. g3 -\> 7.1

If I now want to map the same with a parent/child structure I have the following query:

```auto
{
  "size": 0,
  "query": {
    "has_child" : {
			"type" : "dataPoint",
			"score_mode" : "max",
			"query" : {
				"function_score" : {
					"script_score": {
						"script": "_score * doc['dp.temp'].value"
					}
				}
			}
		}
  },
	"sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ],
  "aggregations": {
    "group": {
      "terms": {
        "field": "group.keyword",
        "size": 20,
        "min_doc_count": 1,
        "shard_min_doc_count": 0,
        "show_term_doc_count_error": false,
        "order": [
					{
            "_key": "asc"
          }
        ]
      },
      "aggregations": {
        "to-datapoint": {
          "children": {
            "type": "dataPoint"
          },
          "aggregations": {
            "max_temp": {
              "max": {
                "field": "dp.temp"
              }
            }
          }
        }
      }
    }
  }
}

```

But with this I get a search result, this search result is also sorted correctly. But the aggregation result below the search result is not sorted, at least not by the max temperature.

1. g1 -\> 5.7
2. g2 -\> 2.2
3. g3 -\> 7.1

Does anyone have an idea what I am doing wrong or is what I am trying to do even possible?  
The feature is very important for us to be able to switch to the managed solution of Elasticsearch.

---

<div class="post-metadata">

**Author:** ![Emanuel\_Zienecker](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emanuel_zienecker/32/77580_2.png) [@Emanuel\_Zienecker](https://discuss.elastic.co/u/Emanuel_Zienecker)\
**Post date:** [November 30, 2022, 8:36am UTC](https://discuss.elastic.co/t/i-cannot-sort-the-aggregation-result-by-the-values-of-the-grouped-set/320121/2 "2022-11-30T08:36:11Z")

</div>

Ok, just in time. I just read through the documentation for the terms aggregation again. I came across the following section: [Terms aggregation | Elasticsearch Guide [8.5] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#_ordering_by_a_sub_aggregation)

In this it is described that you can specify a path. This actually worked. As for all those who are interested in the solution, the following query now leads to the desired result:

```auto
{
  "size": 0,
  "query": {
    "match_all": {
      "boost": 1.0
    }
  },
	"sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ],
  "aggregations": {
    "group": {
      "terms": {
        "field": "group.keyword",
        "size": 20,
        "min_doc_count": 1,
        "shard_min_doc_count": 0,
        "show_term_doc_count_error": false,
        "order": [
					{
            "to-datapoint>max_temp": "asc"
          },
					{
            "_key": "asc"
          }
        ]
      },
      "aggregations": {
        "to-datapoint": {
          "children": {
            "type": "dataPoint"
          },
          "aggregations": {
            "max_temp": {
              "max": {
                "field": "dp.temp"
              }
            }
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 28, 2022, 8:36am UTC](https://discuss.elastic.co/t/i-cannot-sort-the-aggregation-result-by-the-values-of-the-grouped-set/320121/3 "2022-12-28T08:36:53Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.