Global Aggregation on nested fields with filters

Elastic Stack Elasticsearch
1

I am facing issues with aggregations on nested field. Below are the mapping fields.

{
  "mappings": {
    "properties": {
      "autocomplete": {
        "type": "text",
        "analyzer": "autocomplete",
        "fielddata": true
      },   
      "committeeName": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword"
          },
          "sort": {
            "type": "keyword",
            "normalizer": "lowercase_normalizer"
          }
        },
        "copy_to": [
          "did_you_mean",
          "autocomplete"
        ]
      },
      "did_you_mean": {
        "type": "text",
        "analyzer": "didYouMean"
      },
      "meetings": {
        "type": "nested",
        "properties": {
          "endDate": {
            "type": "date",
            "format": "iso8601"
          },
          "meetingDateName": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword"
              }
            }
          },
          "meetingId": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword"
              }
            }
          },
          "meetingTitle": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword"
              }
            }
          },
          "year": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword"
              }
            }
          }
        }
      },
      "roles": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword"
          }
        }
      }
    }
  }
}

Below is the aggregation query

  {
  "aggregations": {
    "sactracker": {
      "global": {},
      "aggregations": {
        "committeeName": {
          "filters": {
            "filters": [
              {
                "match_all": {}
              }
            ]
          },
          "aggregations": {
            "committeeName": {
              "terms": {
                "field": "committeeName.keyword",
                "min_doc_count": 0,
                "size": 10000
              }
            }
          }
        },
        "meetings.meetingDateName": {
          "filters": {
            "filters": [
              {
                "bool": {
                  "must": [
                    {
                      "match_all": {}
                    },
                    {
                      "term": {
                        "committeeName.keyword": {
                          "value": "CommitteeA"
                        }
                      }
                    }
                  ]
                }
              }
            ]
          },
          "aggregations": {
            "meetings.meetingDateName": {
              "terms": {
                "field": "meetings.meetingDateName.keyword",
                "min_doc_count": 0,
                "size": 10000
              }
            }
          }
        },
        "meetings.year": {
          "filters": {
            "filters": [
              {
                "bool": {
                  "must": [
                    {
                      "match_all": {}
                    },
                    {
                      "term": {
                        "committeeName.keyword": {
                          "value": "CommitteeA"
                        }
                      }
                    }
                  ]
                }
              }
            ]
          },
          "aggregations": {
            "meetings.year": {
              "terms": {
                "field": "meetings.year.keyword",
                "min_doc_count": 0,
                "size": 10000
              }
            }
          }
        }
      }
    }
  },
  "explain": true,
  "from": 0
}

Below is the response from elastic.

  "aggregations": {
    "sactracker": {
      "doc_count": 54,
      "meetings.meetingDateName": {
        "buckets": [
          {
            "doc_count": 1,
            "meetings.meetingDateName": {
              "doc_count_error_upper_bound": 0,
              "sum_other_doc_count": 0,
              "buckets": [
                {
                  "key": "28 Jan 2010",
                  "doc_count": 0
                },
                {
                  "key": "28 Jan 2014",
                  "doc_count": 0
                },
                {
                  "key": "28 Jul 2009",
                  "doc_count": 0
                },
                {
                  "key": "28 Jul 2010",
                  "doc_count": 0
                },
                {
                  "key": "28 Jul 2017",
                  "doc_count": 0
                },
                {
                  "key": "28 Jun 2005",
                  "doc_count": 0
                }
              ]
            }
          }
        ]
      },
      "meetings.year": {
        "buckets": [
          {
            "doc_count": 1,
            "meetings.year": {
              "doc_count_error_upper_bound": 0,
              "sum_other_doc_count": 0,
              "buckets": [
                {
                  "key": "1900",
                  "doc_count": 0
                },
                {
                  "key": "2000",
                  "doc_count": 0
                },
                {
                  "key": "2005",
                  "doc_count": 0
                },
                {
                  "key": "2006",
                  "doc_count": 0
                },
                {
                  "key": "2007",
                  "doc_count": 0
                },
                {
                  "key": "2008",
                  "doc_count": 0
                },
                {
                  "key": "2009",
                  "doc_count": 0
                },
                {
                  "key": "2010",
                  "doc_count": 0
                },
                {
                  "key": "2011",
                  "doc_count": 0
                },
                {
                  "key": "2012",
                  "doc_count": 0
                },
                {
                  "key": "2013",
                  "doc_count": 0
                },
                {
                  "key": "2014",
                  "doc_count": 0
                },
                {
                  "key": "2015",
                  "doc_count": 0
                },
                {
                  "key": "2016",
                  "doc_count": 0
                },
                {
                  "key": "2017",
                  "doc_count": 0
                },
                {
                  "key": "2018",
                  "doc_count": 0
                },
                {
                  "key": "2019",
                  "doc_count": 0
                },
                {
                  "key": "2020",
                  "doc_count": 0
                },
                {
                  "key": "2021",
                  "doc_count": 0
                },
                {
                  "key": "2022",
                  "doc_count": 0
                },
                {
                  "key": "2023",
                  "doc_count": 0
                },
                {
                  "key": "2024",
                  "doc_count": 0
                }
              ]
            }
          }
        ]
      },
      "committeeName": {
        "buckets": [
          {
            "doc_count": 2,
            "committeeName": {
              "doc_count_error_upper_bound": 0,
              "sum_other_doc_count": 0,
              "buckets": [
                {
                  "key": "CommiteeA",
                  "doc_count": 1
                },
                {
                  "key": "CommiteeB",
                  "doc_count": 1
                }
              ]
            }
          }
        ]
      }
    }
  }

we have filtering based on one of the non-nested fields (commiteeName) but for nested field the aggregation count is always coming zero. However for non nested field the aggregation count is coming fine.

Can somebody please help?

2

Added aggregations

3

Trying to understand and help, but if some documents lack the "meetings" field entirely, the nested aggregation won't return any results for those documents. You can use a filter aggregation to exclude documents missing the nested field before the nested aggregation.

If that's the case, try this query:


POST /example_index/_search
{
  "aggregations": {
    "sactracker": {
      "global": {},
      "aggregations": {
        "committeeName": {
          "filters": {
            "filters": [
              {
                "match_all": {}
              }
            ]
          },
          "aggregations": {
            "committeeName": {
              "terms": {
                "field": "committeeName.keyword",
                "min_doc_count": 0,
                "size": 10000
              }
            }
          }
        },
        "meetings.meetingDateName": {
          "filters": {
            "filters": [
              {
                "bool": {
                  "must": [
                    {
                      "match_all": {}
                    },
                    {
                      "term": {
                        "committeeName.keyword": {
                          "value": "Advisory Committee on Blood and Tissue Safety and Availability"
                        }
                      }
                    },
                    {
                      "exists": {
                        "field": "meetings"
                      }
                    }
                  ]
                }
              }
            ]
          },
          "aggregations": {
            "meetings.meetingDateName": {
              "terms": {
                "field": "meetings.meetingDateName.keyword",
                "min_doc_count": 0,
                "size": 10000
              }
            }
          }
        },
        "meetings.year": {
          "filters": {
            "filters": [
              {
                "bool": {
                  "must": [
                    {
                      "match_all": {}
                    },
                    {
                      "term": {
                        "committeeName.keyword": {
                          "value": "Advisory Committee on Blood and Tissue Safety and Availability"
                        }
                      }
                    },
                    {
                      "exists": {
                        "field": "meetings"
                      }
                    }
                  ]
                }
              }
            ]
          },
          "aggregations": {
            "meetings.year": {
              "terms": {
                "field": "meetings.year.keyword",
                "min_doc_count": 0,
                "size": 10000
              }
            }
          }
        }
      }
    }
  }
}
4

Have tried, seems the provided solution not working. Any examples to find global aggregations of nested fields by applying non nested field filter, like the way I am trying to achieve above?

5

Hi @Alex_Salgado-Elastic - Any idea how we can achieve the above?