How to build two nested aggregations in elasticsearch

Question

I have a mapping like this:

"location": {
   "type": "nested",
   "properties": {
     "address": {
       "type": "nested",
       "properties": {
         "countryId": {
           "type": "long"
         }
       }
     }
    }
}

How can I write a aggregation to count how many time the countryId have been used. I have tried this query but it's not really work:

{
  "aggregations": {
    "addresses": {
      "nested": {
        "path": "location.address"
      },
      "aggregations": {
        "address.countryId": {
          "terms": {
            "field": "address.countryId.keyword",
            "size": 1000,
            "min_doc_count": 1,
            "shard_min_doc_count": 0,
            "show_term_doc_count_error": false,
            "order": [
              {
                "_count": "desc"
              },
              {
                "_key": "asc"
              }
            ],
            "include": [
              "1",
              "2",
              "3",
              "4",
              "5",
              "6",
              "7",
              "8",
              "9"
            ],
            "exclude": []
          }
        }
      }
    }
  }
}

I think the problem come from the nested path but I can't fix it correctly

Answer 1

You need to use the below query to get the count of unique countryId in your index.

Adding a working example with index data (using the same index mapping as given in the question), search query, and search result:

Index Documents:

{
    "location": {
        "address": {
            "countryId": 2
        }
    }
}
{
    "location": {
        "address": {
            "countryId": 1
        }
    }
}
{
    "location": {
        "address": {
            "countryId": 1
        }
    }
}

Nested with terms aggregation will return you buckets of unique countryId documents

Search Query:

{
    "size": 0,
    "aggregations": {
        "addresses": {
            "nested": {
                "path": "location.address"
            },
            "aggregations": {
                "address.countryId": {
                    "terms": {
                        "field": "location.address.countryId"
                    }
                }
            }
        }
    }
}

Search Result:

 "aggregations": {
        "addresses": {
            "doc_count": 2,
            "address.countryId": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                    {
                        "key": 1,
                        "doc_count": 1
                    },
                    {
                        "key": 2,
                        "doc_count": 1
                    }
                ]
            }
        }
    }

---

Cardinality aggregation will return you a count of unique countryId documents

{
    "size": 0,
    "aggregations": {
        "addresses": {
            "nested": {
                "path": "location.address"
            },
            "aggregations": {
                "address.countryId": {
                    "cardinality": {
                        "field": "location.address.countryId"
                    }
                }
            }
        }
    }
}

Search Result:

"aggregations": {
        "addresses": {
            "doc_count": 2,
            "address.countryId": {
                "value": 2
            }
        }
    }