Elasticsearch: How to aggregate from nested documents up to each main document?

John_27109 0
Viewed 36

Is there a syntax to aggregate nested documents up to each main document? For example, how would I get back the youngest player for each team in the example below? I'm using Elasticsearch 7.10.

PUT sample
{
  "mappings": {
    "dynamic": "strict",
    "properties": {
      "teamId": { "type": "keyword", "index": true, "doc_values": true },
      "members": {
        "type": "nested",
        "properties": {
          "memberId": { "type": "keyword", "index": true, "doc_values": true },
          "age": { "type": "integer", "index": true, "doc_values": true}
        }
      }
    }
  }
}

PUT sample/_doc/1
{
      "teamId" : "A"
    , "members" :
      [
          { "memberId" : "A1" , "age" : "11" }
        , { "memberId" : "A2" , "age" : "21" }
        , { "memberId" : "A3" , "age" : "31" }
      ]                                      
}                                            
                                             
PUT sample/_doc/2                            
{                                            
      "teamId" : "B"                         
    , "members" :                            
      [                                      
          { "memberId" : "B1" , "age" : "12" }
        , { "memberId" : "B2" , "age" : "22" }
        , { "memberId" : "B3" , "age" : "32" }
        , { "memberId" : "B4" , "age" : "42" }
      ]                                      
}                                            
                                             
                                             
PUT sample/_doc/3                            
{                                            
      "teamId" : "C"                         
    , "members" :                            
      [                                      
          { "memberId" : "C1" , "age" : "13" }
        , { "memberId" : "C2" , "age" : "23" }
        , { "memberId" : "C3" , "age" : "33" }
      ]
}
elasticsearch-aggregation elasticsearch
1 Answers

You can use the nested aggregation with top hits aggregation, below is the working query which works fine on your sample data.

{
    "size": 0,
    "aggs": {
        "teams": {
            "terms": {
                "field": "teamId"
            },
            "aggs": {
                "members": {
                    "nested": {
                        "path": "members"
                    },
                    "aggs": {
                        "min_age_player": {
                            "top_hits": {
                                "sort": [
                                    {
                                        "members.age": {
                                            "order": "asc"
                                        }
                                    }
                                ],
                                "_source": {
                                    "includes": [
                                        "members.memberId",
                                        "members.age"
                                    ]
                                },
                                "size": 1
                            }
                        }
                    }
                }
            }
        }
    }
}
user_40394310
Related