Aggregation based on either of the three mappings

Elastic Stack Elasticsearch
1

I have the following data structure

[ {"user_id":166384,"prog_id":14,
"events":[{"country_iso":"AE"}],
"branches":[{"country_iso":"AE"}],
"groups":[{"country_iso":"KW"}]},

{"user_id":17788,"prog_id":14,
"events":[{"country_iso":"AE"}],
"branches":[{"country_iso":"IN"}],
"groups":[{"country_iso":"KW"}]}
]

I need to find number of users with each country_iso

The result should be like [

{
    "key": "AE",
    "count": 2
  },
  {
    "key": "KW",
    "count": 2
  },
  {
    "key": "IN",
    "count": 1
  }
]

Just started learning Elasticsearch today , I am trying to find a single query which can give the expected result.
Can someone please help me with the query ?
Thanks in advance

2

You need to copy all of the country_iso fields into a single field, then run an aggregation on that field.

Example below. (I assume that country_iso isn't the only field that you will have under events etc, and that you will want to be able to query each object under events independently, so I have made them nested fields instead of object fields. You can read more about this distinction here: https://www.elastic.co/guide/en/elasticsearch/reference/5.4/nested.html)

PUT t
{
  "mappings": {
    "t": {
      "properties": {
        "user_id": {
          "type": "keyword"
        },
        "prog_id": {
          "type": "keyword"
        },
        "country_iso": {
          "type": "keyword"
        },
        "events": {
          "type": "nested",
          "properties": {
            "country_iso": {
              "type": "keyword",
              "copy_to": "country_iso"
            }
          }
        },
        "branches": {
          "type": "nested",
          "properties": {
            "country_iso": {
              "type": "keyword",
              "copy_to": "country_iso"
            }
          }
        },
        "groups": {
          "type": "nested",
          "properties": {
            "country_iso": {
              "type": "keyword",
              "copy_to": "country_iso"
            }
          }
        }
      }
    }
  }
}

PUT t/t/1
{
  "user_id": 166384,
  "prog_id": 14,
  "events": [
    {
      "country_iso": "AE"
    }
  ],
  "branches": [
    {
      "country_iso": "AE"
    }
  ],
  "groups": [
    {
      "country_iso": "KW"
    }
  ]
}

PUT t/t/2
{
  "user_id": 17788,
  "prog_id": 14,
  "events": [
    {
      "country_iso": "AE"
    }
  ],
  "branches": [
    {
      "country_iso": "IN"
    }
  ],
  "groups": [
    {
      "country_iso": "KW"
    }
  ]
}

GET t/_search
{
  "size": 0,
  "aggs": {
    "country_iso": {
      "terms": {
        "field": "country_iso",
        "size": 10
      }
    }
  }
}
3

Thanks a lot , I will try this out in couple of hours.