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

[Emanuel_Zienecker]

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:

{
  "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:

{
  "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.

[Emanuel_Zienecker]

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

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:

{
  "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"
              }
            }
          }
        }
      }
    }
  }
}