# Nested terms facet : return document count instead of terms count

**URL:** <https://discuss.elastic.co/t/nested-terms-facet-return-document-count-instead-of-terms-count/2136>\
**Category:** Elasticsearch\
**Created:** [June 8, 2015, 2:13pm UTC](https://discuss.elastic.co/t/nested-terms-facet-return-document-count-instead-of-terms-count/2136 "2015-06-08T14:13:20Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Damien\_Ferey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/damien_ferey/32/536_2.png) [@Damien\_Ferey](https://discuss.elastic.co/u/Damien_Ferey)\
**Post date:** [June 8, 2015, 2:13pm UTC](https://discuss.elastic.co/t/nested-terms-facet-return-document-count-instead-of-terms-count/2136/1 "2015-06-08T14:13:20Z")

</div>

I'm trying to do the following:

A player can play to many games.  
Every game session has a begining date and a end date.

- a player can have simultaneous game sessions
- a player can have multiple sessions of a same game

(to simplify the samples I set only full years as dates)

I would like a facet/aggregation that filters only the active sessions at a given date.

ie:  
Player1:

- HearthStone: 2015-2016
- Magic : 2005-2016

Player2:

- HearthStone: 2014-2015
- Magic : 2005-2008 & 2009-2010

So if I choose the date 2015, I should have the following result:

- HearthStone : 2
- Magic : 1

I can't manage to get what I want.

I provide my test in the following gist:

- [https://gist.github.com/MarneusCalgarXP/cce1cee54645e196c93b](https://gist.github.com/MarneusCalgarXP/cce1cee54645e196c93b)

Can you give me a clue on how to rearrange my data and/or my facet/aggregation query ?

---

<div class="post-metadata">

**Author:** ![dantuff](https://avatars.discourse-cdn.com/v4/letter/d/e79b87/32.png) [@dantuff](https://discuss.elastic.co/u/dantuff)\
**Post date:** [June 9, 2015, 9:28am UTC](https://discuss.elastic.co/t/nested-terms-facet-return-document-count-instead-of-terms-count/2136/2 "2015-06-09T09:28:20Z")

</div>

Facets are deprecated, so use aggregations.

To get the results you want you need to use a nested [filter aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/1.4/search-aggregations-bucket-filter-aggregation.html) (to filter out games that are not within the required date range) and for the counts of the `name` field use a nested [terms aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html). To get the counts of the nested documents I would recommend moving the `name` field into the same object as the `beginDate` and `endDate` fields:

```
{
  "player":{
	"properties":{
	  "games":{
		"type":"nested",
		"properties":{
		  "name":{
			"type":"string",
			"index":"not_analyzed"
		  },
		  "beginDate":{
			"type":"date",
			"format":"dd/MM/yyyy"
		  },
		  "endDate":{
			"type":"date",
			"format":"dd/MM/yyyy"
		  }
	      }
	  }
      }
   }
}

```

I noticed in your example you have used `beginDate` in your mapping but `startDate` in the documents.

Your docs will now be:

```
{
  "games":[
	{
	  "name":"HearthStone",
	  "beginDate":"01/01/2014",
	  "endDate":"01/01/2015"
	},
	{
	  "name":"Magic",
	  "beginDate":"01/01/2005",
	  "endDate":"01/01/2008"
	},
	{
	  "name":"Magic",
	  "beginDate":"01/01/2009",
	  "endDate":"01/01/2010"
	}
    ]
}

```

So the trade-off here is that the `name` field is duplicated in the data.

With this mapping and data you can return the counts of the nested docs that are within the date range :

```
{
  "aggs":{
	"user_games":{
	  "nested":{
		"path":"games"
	  },
	  "aggs":{
		"games_filter":{
		  "filter":{
			"bool":{
			  "must":[
				{
				  "range":{
					"games.beginDate":{
					  "lte":"01/01/2015"
					}
				  }
				},
				{
				  "range":{
					"games.endDate":{
					  "gte":"01/01/2015"
					}
				    }
				}
			    ]
			}
		  },
		  "aggs":{
			"game_name":{
			  "terms":{
				"field":"games.name"
			  }
		      }
		  }
	      }
	  }
       }
   }
}

```

---

<div class="post-metadata">

**Author:** ![Damien\_Ferey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/damien_ferey/32/536_2.png) [@Damien\_Ferey](https://discuss.elastic.co/u/Damien_Ferey)\
**Post date:** [June 9, 2015, 11:51am UTC](https://discuss.elastic.co/t/nested-terms-facet-return-document-count-instead-of-terms-count/2136/3 "2015-06-09T11:51:36Z")

</div>

Thank you a lot, I think I can figure now how to get the result I wanted to !

---

<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:** [July 6, 2017, 12:08am UTC](https://discuss.elastic.co/t/nested-terms-facet-return-document-count-instead-of-terms-count/2136/4 "2017-07-06T00:08:51Z")

</div>

Closed on July 6, 2017, 12:08am UTC