# Aggregating by a given item in array

**URL:** <https://discuss.elastic.co/t/aggregating-by-a-given-item-in-array/197995>\
**Category:** Elasticsearch\
**Created:** [September 4, 2019, 7:56am UTC](https://discuss.elastic.co/t/aggregating-by-a-given-item-in-array/197995 "2019-09-04T07:56:08Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Yonatan\_Omer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yonatan_omer/32/42450_2.png) [@Yonatan\_Omer](https://discuss.elastic.co/u/Yonatan_Omer)\
**Post date:** [September 4, 2019, 7:56am UTC](https://discuss.elastic.co/t/aggregating-by-a-given-item-in-array/197995/1 "2019-09-04T07:56:08Z")

</div>

My big documents contain arrays of objects, for example:

```auto
_doc : {
   countries: {
	[
		{"country":"czechia", "name":"novak", ...}, 
		{"country":"england", "name":"jonson", ...} 
	]
   },
   ...
}

```

In my query, I specify a "country"+ a filter query, and need to get the count of each distinct "name".  
If I'd do terms aggregation on the "name" field, I would get the count of \_docs each name appears in. but I need the count of **name** at all docs for a given country.

I need the following query to return only names for 'england'

```auto
POST /stats/_search?size=0
{
  "query": {
    "term" : {"countries.country" : "england"}
  },
    
  "aggs" : {
      "top-messages": {"terms": {"field": "countries.name","size": 10}}
  }
}

```

Is that possible without fetching the whole document and doing (huge) aggregation offline?

---

<div class="post-metadata">

**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\
**Post date:** [September 9, 2019, 5:46pm UTC](https://discuss.elastic.co/t/aggregating-by-a-given-item-in-array/197995/2 "2019-09-09T17:46:14Z")

</div>

It's possible, but you'll need to use the [`nested` datatypes](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html) instead. Regular json objects (like your example) are "flattened" internally and lose their relationship. E.g. internally that data will be stored as:

```json
{
  countries.country: ["czechia","england"],
  countries.name: ["novak","jonson"]
}

```

The `nested` data type internally uses a different storage mechanism to keep the relations between items in an object, so that you can run queries/aggregrations like you requested.

---

<div class="post-metadata">

**Author:** ![Yonatan\_Omer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yonatan_omer/32/42450_2.png) [@Yonatan\_Omer](https://discuss.elastic.co/u/Yonatan_Omer)\
**Post date:** [September 10, 2019, 2:20pm UTC](https://discuss.elastic.co/t/aggregating-by-a-given-item-in-array/197995/3 "2019-09-10T14:20:00Z")

</div>

Thank you!  
Yes, I did tried that eventually, but since I have more then 200k distinct nested terms, the nested aggregations had too many buckets.  
The solution I found is to store everything flat with string prefixes, and use script regexp to query subsets of the tree :

names: ["czechia/novak","czechia/benes","england/jonson",...]

{  
"aggs": {  
"evt\_buckets": {  
"terms": {  
"field": "names",  
"include": "czechia.\*"  
}  
}  
}  
}

I considered also

---

<div class="post-metadata">

**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\
**Post date:** [September 12, 2019, 5:17pm UTC](https://discuss.elastic.co/t/aggregating-by-a-given-item-in-array/197995/4 "2019-09-12T17:17:51Z")

</div>

Yeah agreed, 200k nested objects would be too many 🙂

I'd probably try to restructure things so that each doc is one of the distinct pairs and they are all related by some kind of identifier. Basically denormalize each relation to it's own document. ES tends to be easier to work with when you're more denormalized... the relational tools are only rough approximations of real RDBMs functionality.

---

<div class="post-metadata">

**Author:** ![Yonatan\_Omer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yonatan_omer/32/42450_2.png) [@Yonatan\_Omer](https://discuss.elastic.co/u/Yonatan_Omer)\
**Post date:** [September 18, 2019, 6:54am UTC](https://discuss.elastic.co/t/aggregating-by-a-given-item-in-array/197995/5 "2019-09-18T06:54:43Z")

</div>

Yes, my case is problematic since I have large documents (2MB each),  
Each document contain a list of all events occurred on a single client per week.  
I tried to store it flat (\_doc = event), but i cannot make queries such as  
"for a given group of clients, find significant event id's".  
Further reducing the time window will break relations between events.

---

<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:** [October 16, 2019, 6:54am UTC](https://discuss.elastic.co/t/aggregating-by-a-given-item-in-array/197995/6 "2019-10-16T06:54:57Z")

</div>

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