# Watcher aggregation to nested field transformation

**URL:** <https://discuss.elastic.co/t/watcher-aggregation-to-nested-field-transformation/157729>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-alerting\
**Created:** [November 21, 2018, 2:43pm UTC](https://discuss.elastic.co/t/watcher-aggregation-to-nested-field-transformation/157729 "2018-11-21T14:43:10Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![bodi4](https://avatars.discourse-cdn.com/v4/letter/b/57b2e6/32.png) [@bodi4](https://discuss.elastic.co/u/bodi4)\
**Post date:** [November 21, 2018, 2:43pm UTC](https://discuss.elastic.co/t/watcher-aggregation-to-nested-field-transformation/157729/1 "2018-11-21T14:43:10Z")

</div>

Hi all,  
Have searched all over discuss on different categories. Some people raised similar question, but there is no clear answer. So, hope if we find a solution it will help many people.  
Sometimes aggregation in query is used several times so we get nested buckets.  
If we use **action index** we need to tranform data.  
It is easy to make multiple docs with one bucket. Like:

`"script": "return ['_doc' : ctx.payload.aggregations.agg_name.buckets]"`  
or  
`"script": "def docs = ; for(item in ctx.payload.aggregations.agg_name.buckets) { def document = [agg_name.key : agg_name.doc_count]; docs.add(document);} return ['_doc' : docs];"`

But how to generate a correct mapping for nested field from sub bucket (i.e second bucket) to the doc for first bucket? Because we get an array.

My best was to get:

`"script": "def docs = []; def subdocs = []; for(item in ctx.payload.aggregations.agg1.buckets) { for(subitem in item.agg2.buckets) { def subdocument = [subitem.key : subitem.doc_count]; subdocs.add(subdocument);} def document = ['a' : item.key, 'b' : item.doc_count, 'c' : subdocs]; subdocs = []; docs.add(document);} return ['_doc' : docs];"`

And I get 'c' field not nested but an array.

Can anyone give a clue?

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [November 27, 2018, 8:14am UTC](https://discuss.elastic.co/t/watcher-aggregation-to-nested-field-transformation/157729/2 "2018-11-27T08:14:12Z")

</div>

I'd be extremely helpful to also specify how the document now looks like and how you would like it to look like along with your script.

Your best way of debugging this is probably using a `logging` action with the above `transform`, as you can watch directly in the [Execute watch API response](https://www.elastic.co/guide/en/elasticsearch/reference/6.5/watcher-api-execute-watch.html), how your data structure looks like.

hope this helps!

--Alex

---

<div class="post-metadata">

**Author:** ![bodi4](https://avatars.discourse-cdn.com/v4/letter/b/57b2e6/32.png) [@bodi4](https://discuss.elastic.co/u/bodi4)\
**Post date:** [November 27, 2018, 8:51am UTC](https://discuss.elastic.co/t/watcher-aggregation-to-nested-field-transformation/157729/3 "2018-11-27T08:51:42Z")

</div>

Alexander, thanks for interesting!  
with a watcher like this:

> ```
> {
> "trigger": {
> "schedule": {
> "interval": "30s"
> }
> },
> "input": {
> "search": {
> "request": {
> "search_type": "query_then_fetch",
> "indices": [
> "log-ws-2*"
> ],
> "types": [],
> "body": {
> "size": 0,
> "query": {
> "range": {
> "@timestamp": {
> "gte": "now-30s"
> }
> }
> },
> "aggs": {
> "services": {
> "terms": {
> "field": "beat.name.keyword"
> },
> "aggs": {
> "methods": {
> "significant_terms": {
> "field": "m_method.keyword"
> }
> }
> }
> }
> }
> }
> }
> }
> },
> "condition": {
> "compare": {
> "ctx.payload.hits.total": {
> "gt": 0
> }
> }
> },
> "actions": {
> "ws-aggs-test": {
> "transform": {
> "script": {
> "source": "def docs = []; def subdocs = []; for(item in ctx.payload.aggregations.services.buckets) { for(method in item.methods.buckets) { def subdocument = [method.key : method.doc_count]; subdocs.add(subdocument);} def document = ['service' : item.key, 'count_overal' : item.doc_count, 'methods' : subdocs]; subdocs = []; docs.add(document);} return ['_doc' : docs];",
> "lang": "painless"
> }
> },
> "index": {
> "index": "log-ws-stat-2018.11",
> "doc_type": "doc",
> "execution_time_field": "@timestamp"
> }
> }
> }
> }
> 
> ```

I get resulting documents like:

> ```
> {
> "_index": "log-ws-stats-2018.11",
> "_type": "doc",
> "_id": "1rdSVGcBX-k3fJp0qD58",
> "_version": 1,
> "_score": null,
> "_source": {
> "@timestamp": "2018-11-27T08:39:22.170Z",
> "service": "SERVICE1",
> "methods": [
> {
> "method1": 136
> },
> {
> "method2": 135
> },
> {
> "method3": 147
> },
> {
> "method4": 146
> },
> {
> "method5": 126
> },
> {
> "method6": 123
> },
> {
> "method7": 77
> },
> {
> "method8": 76
> }
> ],
> "count_overal": 966
> },
> "fields": {
> "@timestamp": [
> "2018-11-27T08:39:22.170Z"
> ]
> },
> "sort": [
> 1543307962170
> ]
> }
> 
> ```

I get `"methods"` field as an array. I'm trying to make it nested, because array fields cannot be indexed.  
Hope that clarifies.

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [November 27, 2018, 8:54am UTC](https://discuss.elastic.co/t/watcher-aggregation-to-nested-field-transformation/157729/4 "2018-11-27T08:54:42Z")

</div>

arrays can be indexed in just fine in elasticsearch.

If you are talking about `nested` in the mapping, you need to configure this first, but I am not sure what you mean.

Can you also show what kind of end document you would like to have?

---

<div class="post-metadata">

**Author:** ![bodi4](https://avatars.discourse-cdn.com/v4/letter/b/57b2e6/32.png) [@bodi4](https://discuss.elastic.co/u/bodi4)\
**Post date:** [November 27, 2018, 9:04am UTC](https://discuss.elastic.co/t/watcher-aggregation-to-nested-field-transformation/157729/5 "2018-11-27T09:04:55Z")

</div>

Sure,  
I want to make it like `"beat"` field:

> ```
> "methods": {
> "method1": 136,
> "method2": 135,
> "method3": 147,
> "method4": 146,
> "method5": 126,
> "method6": 123,
> "method7": 77,
> "method8": 76,
> },
> "count_overal": 966
> 
> ```

But I'm not sure if it is necessary.  
The goal is to get all methods for service in one document.  
Will try to work it around.

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [November 27, 2018, 9:17am UTC](https://discuss.elastic.co/t/watcher-aggregation-to-nested-field-transformation/157729/6 "2018-11-27T09:17:00Z")

</div>

See this line

```auto
def subdocument = [method.key : method.doc_count]; subdocs.add(subdocument)

```

you are creating an own map, for each subdocument and add to a list. subdocs needs to be a map (`[:]`), and then you can do `subdocs[method.key] = method.doc_count`

---

<div class="post-metadata">

**Author:** ![bodi4](https://avatars.discourse-cdn.com/v4/letter/b/57b2e6/32.png) [@bodi4](https://discuss.elastic.co/u/bodi4)\
**Post date:** [November 27, 2018, 9:51am UTC](https://discuss.elastic.co/t/watcher-aggregation-to-nested-field-transformation/157729/7 "2018-11-27T09:51:34Z")

</div>

Worked like a charm!  
Thank you for help!

For everyone to save time. The resulting transformation is:

> ```
> "transform": {
> "script": {
> "source": "def docs = []; def subdocs = [:]; for(item in ctx.payload.aggregations.services.buckets) { for(method in item.methods.buckets) { subdocs[method.key] = method.doc_count } def document = ['service' : item.key, 'count_overal' : item.doc_count, 'methods' : subdocs]; subdocs = [:]; docs.add(document);} return ['_doc' : docs];",
> "lang": "painless"
> }
> }
> 
> ```

---

<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:** [December 25, 2018, 9:56am UTC](https://discuss.elastic.co/t/watcher-aggregation-to-nested-field-transformation/157729/8 "2018-12-25T09:56:28Z")

</div>

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