Count of nested documents by aggregation : elasticsearch

Question

I have 2 level of nesting in my mapping: business -> funds -> admins/market I want count of f_id's that matches with particular admin_name

ES MAPPING

{
  "text_index" : {
    "mappings" : {
      "properties" : {
        "buisness_id" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
        },
        "buisness_type" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "funds" : {
          "type" : "nested",
          "properties" : {
            "f_id" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            }
            "admins" : {
              "type" : "nested",
              "properties" : {
                "admin_name" : {
                  "type" : "text",
                  "fields" : {
                    "keyword" : {
                      "type" : "keyword",
                      "ignore_above" : 256
                    }
                  },
                },
                "admin_id" : {
                  "type" : "text",
                    "fields" : {
                      "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                      }
                    }
                }
              }
            },
            "market" : {
              "type" : "nested",
              "properties" : {
                "mkt_name" : {
                  "type" : "text",
                  "fields" : {
                    "keyword" : {
                      "type" : "keyword",
                      "ignore_above" : 256
                    }
                  }
                },
                "mkt_id" : {
                  "type" : "text",
                  "fields" : {
                    "keyword" : {
                      "type" : "keyword",
                      "ignore_above" : 256
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

AGGS QUERY FOR COUNT:

{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "funds",
            "query": {
              "nested": {
                "path": "funds.admins",
                "query": {
                  "bool": {
                    "must": [
                      {
                        "match": {
                          "funds.admins.admin_name": "NOMAD"
                        }
                      }
                    ]
                  }
                }
              }
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "funds_count": {
      "nested": {
        "path": "funds"
      },
      "aggs": {
        "f_count": {
          "value_count": {
            "field": "funds.f_id.keyword"
          }
        }
      }
    }
  }
}

But it is not giving the correct count of f_id's.

Answer 1

I'm assuming you would want the distinct counts of f_ids instead of total count

For that just make use of Cardinality Aggregation instead of Value Count Aggregation.

Also you can shorten your nested query i.e. below should help you:

POST <your_index_name>/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "funds.admins",                    <---- Note this
            "query": {
              "match": {
                "funds.admins.admin_name": "nomad"     <---- Also note this to make use of keyword for case sensitivity and exact match
              }
            }
          }
        }
      ]
    }
  }, 
  "aggs": {
    "funds_count": {
      "nested": {
        "path": "funds"
      },
      "aggs": {
        "f_count": {
          "cardinality": {                    <---- Note this
            "field": "funds.f_id.keyword"
          }
        }
      }
    }
  }
}