# Aggregation based on either of the three mappings

**URL:** https://discuss.elastic.co/t/aggregation-based-on-either-of-the-three-mappings/86706
**Category:** Elasticsearch
**Created:** [May 22, 2017, 4:19pm UTC](https://discuss.elastic.co/t/aggregation-based-on-either-of-the-three-mappings/86706 "2017-05-22T16:19:01Z")
**Posts on this page:** 4
**Page:** 1

<div class="post-metadata">

### Author: ![Javid\_Ahammed](https://avatars.discourse-cdn.com/v4/letter/j/65b543/32.png) [@Javid\_Ahammed](https://discuss.elastic.co/u/Javid_Ahammed)
#### Post date: [May 22, 2017, 4:19pm UTC](https://discuss.elastic.co/t/aggregation-based-on-either-of-the-three-mappings/86706/1 "2017-05-22T16:19:01Z")

</div>

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

---

<div class="post-metadata">

### Author: ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)
#### Post date: [May 26, 2017, 8:46am UTC](https://discuss.elastic.co/t/aggregation-based-on-either-of-the-three-mappings/86706/2 "2017-05-26T08:46:07Z")

</div>

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](https://www.elastic.co/guide/en/elasticsearch/reference/5.4/nested.html))

```auto
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
      }
    }
  }
}

```

---

<div class="post-metadata">

### Author: ![Javid\_Ahammed](https://avatars.discourse-cdn.com/v4/letter/j/65b543/32.png) [@Javid\_Ahammed](https://discuss.elastic.co/u/Javid_Ahammed)
#### Post date: [May 27, 2017, 9:35am UTC](https://discuss.elastic.co/t/aggregation-based-on-either-of-the-three-mappings/86706/3 "2017-05-27T09:35:58Z")

</div>

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

---

<div class="post-metadata">

### Author: ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)
#### Post date: [June 24, 2017, 9:36am UTC](https://discuss.elastic.co/t/aggregation-based-on-either-of-the-three-mappings/86706/4 "2017-06-24T09:36:13Z")

</div>

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