# Sub aggregations on bucket key
**URL:** https://discuss.elastic.co/t/sub-aggregations-on-bucket-key/224755
**Category:** Elasticsearch
**Created:** [March 24, 2020, 2:05am UTC](https://discuss.elastic.co/t/sub-aggregations-on-bucket-key/224755 "2020-03-24T02:05:56Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Michael\_Jackson1](https://discuss.elastic.co/u/Michael_Jackson1)
#### Post date: [March 24, 2020, 2:05am UTC](https://discuss.elastic.co/t/sub-aggregations-on-bucket-key/224755/1 "2020-03-24T02:05:56Z")
Hello everyone, new member here. I have been testing the path\_hierarchy tokenizer. Just looking for advice with my situation.
I have a simple index with docs that look like this:
```auto
{
"keyword": "stuff and things",
"path": "/foo/bar/baz/",
"views": 14,
"members: 40,
....etc
}
```
The index contains a little over 1 million documents.
For the paths depth, there are 15 different level 1's, ~500 different level 2's and its keeps growing for each level of which all stop somewhere between 2-14 levels. Most of the data stops around level 7.
The goal is to be able to search through each level starting at the top 15 groups and be able to see the document counts as wells as aggregate the metrics associated.
The problem I am running into by just using path\_hierarchy on all the data, I am running out of resources as the amount of data is rather large. I was trying to aggregate the top level and select one of the keys and use the path\_hierarchy tokenizer to aggregate the next level down. So far I am unable to construct a query to do this, but I believe that I am going to run into a situation where I will return the higher levels as well.
I am still working on figuring this all out, but was hoping for a little insight from more experienced elasticsearch queriers. Any help/guidance would be greatly appreciated as I am not entirely sure how to approach this. I have thought about including a level attribute to the documents to limit the return to a range as I traverse down the paths. Again not sure if that is going to best approach and would love some input.
cheers!
---
### Author:  [@spinscale](https://discuss.elastic.co/u/spinscale)
#### Post date: [March 24, 2020, 4:35pm UTC](https://discuss.elastic.co/t/sub-aggregations-on-bucket-key/224755/2 "2020-03-24T16:35:47Z")
Hey,
would it be possible to provide a full reproduction including index creation/mapping a few sample documents, sample queries - and what documents you expect/not expect back from those? This would make things a lot simpler to follow.
--Alex
---
### Author:  [@Michael\_Jackson1](https://discuss.elastic.co/u/Michael_Jackson1)
#### Post date: [March 24, 2020, 5:59pm UTC](https://discuss.elastic.co/t/sub-aggregations-on-bucket-key/224755/3 "2020-03-24T17:59:22Z")
Index creation:
```auto
PUT file_path
{
"mappings": {
"properties": {
"keyword": {
"type": "text"
},
"search_volume": {
"type": "integer"
},
"cpc": {
"type": "integer"
},
"score": {
"type": "float"
},
"category": {
"type": "keyword"
},
"category_path": {
"type": "keyword"
},
"position":{
"type": "float"
},
"url": {
"type": "keyword"
}
}
}
}
```
Some sample documents:
```auto
{
"_index" : "file_path",
"_type" : "_doc",
"_id" : "Tgk1-nABTUHKytkMmmyr",
"_score" : 10.267364,
"_source" : {
"keyword" : "mp4ba movies",
"search_volume" : 20,
"cpc" : 0,
"score" : 24.26,
"category" : "Movies",
"category_path" : "/Business/Arts_and_Entertainment/Models/Individual/B/Bellucci,_Monica/Movies",
"position" : 20.0,
"url" : "http://mysitetester.com/movies"
}
},
{
"_index" : "file_path",
"_type" : "_doc",
"_id" : "5Ak1-nABTUHKytkMGw6m",
"_score" : 10.267364,
"_source" : {
"keyword" : "marital infidelity movies",
"search_volume" : 50,
"cpc" : 0,
"score" : 39.2635,
"category" : "Movies",
"category_path" : "/Arts/Movies/Studios/Warner_Bros./Movies",
"position" : 17.0,
"url" : "http://mysitetester.com/movies"
}
},
{
"_index" : "file_path",
"_type" : "_doc",
"_id" : "8wk1-nABTUHKytkMGw6n",
"_score" : 10.267364,
"_source" : {
"keyword" : "devotional movies",
"search_volume" : 480,
"cpc" : 0,
"score" : 56.548,
"category" : "Movies",
"category_path" : "/Arts/Movies/Studios/Warner_Bros./Movies",
"position" : 9.0,
"url" : "http://mysitetester.com/movies"
}
}
```
The query I am using to aggregate the top level of the category\_path:
```auto
GET file_path/_search?size=0
{
"aggs": {
"tree": {
"path_hierarchy": {
"field": "category_path",
"separator": "/",
"max_depth": 0,
"size": 30
},
"aggs": {
"search_volumes": {
"avg": {
"field": "search_volume"
}
}
}
}
}
}
```
And its response:
```auto
{
"took" : 0,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 10000,
"relation" : "gte"
},
"max_score" : null,
"hits" : []
},
"aggregations" : {
"tree" : {
"buckets" : [
{
"key" : "World",
"doc_count" : 308153,
"path" : [],
"search_volumes" : {
"value" : 53.68258624774057
}
},
{
"key" : "Arts",
"doc_count" : 206891,
"path" : [],
"search_volumes" : {
"value" : 63.65835149909856
}
},
{
"key" : "Regional",
"doc_count" : 107047,
"path" : [],
"search_volumes" : {
"value" : 58.696553850177956
}
},
{
"key" : "Business",
"doc_count" : 90660,
"path" : [],
"search_volumes" : {
"value" : 51.84634899624972
}
},
{
"key" : "Computers",
"doc_count" : 82783,
"path" : [],
"search_volumes" : {
"value" : 56.790524624621
}
},
{
"key" : "Society",
"doc_count" : 64092,
"path" : [],
"search_volumes" : {
"value" : 58.82512638082756
}
},
{
"key" : "Games",
"doc_count" : 57919,
"path" : [],
"search_volumes" : {
"value" : 62.482432362437194
}
},
{
"key" : "Science",
"doc_count" : 46828,
"path" : [],
"search_volumes" : {
"value" : 55.13837874775775
}
},
{
"key" : "Reference",
"doc_count" : 33955,
"path" : [],
"search_volumes" : {
"value" : 55.791783242526876
}
},
{
"key" : "Sports",
"doc_count" : 22052,
"path" : [],
"search_volumes" : {
"value" : 60.563214220932345
}
},
{
"key" : "Recreation",
"doc_count" : 18459,
"path" : [],
"search_volumes" : {
"value" : 60.06121675063655
}
},
{
"key" : "Health",
"doc_count" : 18455,
"path" : [],
"search_volumes" : {
"value" : 67.62015713898673
}
},
{
"key" : "Shopping",
"doc_count" : 11977,
"path" : [],
"search_volumes" : {
"value" : 61.9195123987643
}
},
{
"key" : "Home",
"doc_count" : 9212,
"path" : [],
"search_volumes" : {
"value" : 64.33239253148068
}
},
{
"key" : "News",
"doc_count" : 689,
"path" : [],
"search_volumes" : {
"value" : 60.711175616835995
}
}
]
}
}
}
```
I am using this plugin:
`https://github.com/opendatasoft/elasticsearch-aggregation-pathhierarchy`
The query above is what I am using to see the top level by setting `depth: 0`. The goal is to be able to query through each level of a given top level. For example: `/Business/Arts_and_Entertainment/Models/Individual/B/Bellucci,_Monica/Movies`
`/Business/Arts_and_Entertainment/Actors/Individual/H/Hanks,_Tom/Producer`
`/Business/Financial/Companies/Banks/Wells_Fargo`
Going down from `Business` to `Arts_and_Entertainment` where the buckets returned would be
`Arts_and_Entertainment` and `Financial` with there respective document counts and whatever metrics that are added to the query (i.e. sum of score, cpc .... etc)
I have tried to sub aggregate a bucket using the bucket key returned, but have not got that query to work. What I have found is that I am not able to use `_key` with `bucket_selector` and `bucket_path`.
Let me know if there is anything else that would help explain my situation.
Thanks!
---
### Author:  [@spinscale](https://discuss.elastic.co/u/spinscale)
#### Post date: [March 30, 2020, 2:03pm UTC](https://discuss.elastic.co/t/sub-aggregations-on-bucket-key/224755/4 "2020-03-30T14:03:14Z")
Ah, you're using a plugin, that is an important information.
I've never used it, and being an external plugin you could either hope for the authors to chime in or possible ping them in the respective github repo, if you do not get an answer here.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [April 27, 2020, 2:12pm UTC](https://discuss.elastic.co/t/sub-aggregations-on-bucket-key/224755/5 "2020-04-27T14:12:53Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.