# Use nested aggregation based on parent values

**URL:** <https://discuss.elastic.co/t/use-nested-aggregation-based-on-parent-values/299492>\
**Category:** Elasticsearch\
**Tags:** painless\
**Created:** [March 11, 2022, 7:22pm UTC](https://discuss.elastic.co/t/use-nested-aggregation-based-on-parent-values/299492 "2022-03-11T19:22:35Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![TomTom](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tomtom/32/3676_2.png) [@TomTom](https://discuss.elastic.co/u/TomTom)\
**Post date:** [March 11, 2022, 7:22pm UTC](https://discuss.elastic.co/t/use-nested-aggregation-based-on-parent-values/299492/1 "2022-03-11T19:22:35Z")

</div>

I have an aggregation that uses comments nested within a task, the purpose of this aggregation is to count comments that other users who are not responsible for the task have left on the task.  
Tried using painless for this but I can't get the document's parent data.  
How can I get it to do my calculation?

This is my mapping:

```auto
curl -XPUT 'localhost:9200/tasks?pretty' -H 'Content-Type: application/json' -d '
{
    "settings": {
        "number_of_shards": 1
    },
    "mappings": {
        "task": {
            "properties": {
                "owner_id": {
                    "type": "long",
                    "index": true
                },
                "title": {
                    "type": "text",
                    "index": true
                },
                "comments": {
                    "type": "object",
                    "properties": {
                        "content": {
                            "type": "nested",
                            "properties": {
                                "comment_id": {
                                    "type": "long",
                                    "index": true
                                },
                                "message": {
                                    "type": "text",
                                    "index": true
                                },
                                "user_id": {
                                    "type": "long",
                                    "index": true
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}'

```

This is my query:

```auto
{
    "size": 0,
    "_source": false,
    "stored_fields": "_none_",
    "aggregations": {
        "comments": {
            "nested": {
                "path": "comments.content"
            },
            "aggs": {
                "operators": {
                    "terms": {
                        "field": "comments.content.user_id",
                        "exclude": [
                            0
                        ]
                    },
                    "aggs": {
                        "comment_task_someone_else": {
                            "sum": {
                                "script": {
                                    "source": "(doc['comments.content.user_id'].value !== doc['owner_id'].value)?1:0"
                                }
                            }
                        },
                        "comment_own_task": {
                            "sum": {
                                "script": {
                                    "source": "(doc['comments.content.user_id'].value === doc['owner_id'].value)?1:0"
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

```

I tried adding just the `doc['owner_id'].value` and it always comes to zero.

How can I get this data to count?  
Or is it possible to totally change the way I do the aggregation to get what I need?

---

<div class="post-metadata">

**Author:** ![TomTom](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tomtom/32/3676_2.png) [@TomTom](https://discuss.elastic.co/u/TomTom)\
**Post date:** [March 25, 2022, 7:48pm UTC](https://discuss.elastic.co/t/use-nested-aggregation-based-on-parent-values/299492/2 "2022-03-25T19:48:12Z")

</div>

My result that I'm going to display would be something close to the table below:

| User ID | Comments Own Task | Comments on Tasks from Other Users |
| --- | --- | --- |
| 123 | 10 | 256 |
| 456 | 50 | 512 |

I've been looking for a solution on Google for a few days now, but I can't find a way to use aggregation in a nested way and calculate a good database in the parent above this nested one.

I've tried with `_source['owner_id'].value` and `ctx._source['owner_id'].value`, but nothing works.

---

<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:** [April 22, 2022, 7:48pm UTC](https://discuss.elastic.co/t/use-nested-aggregation-based-on-parent-values/299492/3 "2022-04-22T19:48:51Z")

</div>

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