How to get nested inner objects total count in elasticsearch query

Hi All,

I am trying to get total count of nested inner objects using elasticsearch query and below is the query I tried,

POST transaction*/_search
{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "MonitorName.keyword": {
              "value": "Scenario"
            }
          }
        },
        {
          "nested": {
            "path": "Location",
            "query": {
              "terms": {
                "Location.LocationName.keyword": [
                  "Ecospace",
                  ""
                ]
              }
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "list": {
      "nested": {
        "path": "Location"
      },
      "aggs": {
        "distinct_count": {
          "terms": {
            "field": "Location.LocationName.keyword"
          }
        }
      }
    }
  }
}

Below is the response I got,

{
  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "scenario_list": {
      "doc_count": 2,
      "distinct_count": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": "Ecospace",
            "doc_count": 1
          },
          {
            "key": "Micropolis",
            "doc_count": 1
          }
        ]
      }
    }
  }
}

Since I have given ecospace in terms query and I want the aggregation buckets should show only ecospace with the count.

Is it possible to get the inner object count based on the specified value in elasticseach query?

The Location data is nested type and below is the document structure,

      "MonitorName": "Scenario"
      "Location": [
        {
          "Parent": {
            "LocationID": "3",
            "LocationName": "Bangalore",
            "Longitude": "12.954517",
            "Latitude": "77.350735",
            "LocationCode": "BLR"
          },
          "LocationID": "33",
          "LocationCode": "BGM",
          "Longitude": "12.983600",
          "Latitude": "77.693900",
          "LocationName": "Bagmane"
        },
        {
          "Parent": {
            "LocationID": "3",
            "LocationName": "Bangalore",
            "Longitude": "12.954517",
            "Latitude": "77.350735",
            "LocationCode": "BLR"
          },
          "LocationID": "31",
          "LocationCode": "ECO",
          "Longitude": "12.927773",
          "Latitude": "77.678449",
          "LocationName": "Ecospace"
        }
      ],
      "Status": "Open"

Please share your thoughts and correct me if I am doing anything wrong.

Regards,
Ganeshbabu R

Inner_hits gets the records with in a nested type. try running count aggregation on the same. Look at inner_hits

Hi @ramchjob

https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-filter-aggregation.html

I tried using the filter aggregation in my query and able to get the result I want,

POST transaction*/_search
{
  "size": 0, 
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "MonitorName.keyword": "ScenarioMonitor"
          }
        }
      ]
    }
  },
  "aggs": {
    "LocationName": {
      "nested": {
        "path": "Location"
      },
      "aggs": {
        "LocationID": {
          "filter": {
            "terms": {
              "Location.LocationName.keyword": [ 
                "Ecospace",
                "Bagmane"
              ]
            }
          },
          "aggs": {
            "LocationName": {
              "terms": {
                "field": "Location.LocationName.keyword"
              }
            }
          }
        }
      }
    }
  }
}

Response:-

{
  "took": 2,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 3,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "LocationName": {
      "doc_count": 5,
      "LocationID": {
        "doc_count": 5,
        "LocationName": {
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": [
            {
              "key": "Bagmane",
              "doc_count": 2
            },
            {
              "key": "Ecospace",
              "doc_count": 2
            }
          ]
        }
      }
    }
  }
}

If I am selected two values in filter location I am getting two location count in buckets and same if I filtered one location then I am getting one location count in buckets.

Regards,
Ganeshbabu R

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