Use nested aggregation based on parent values

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:

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:

{
    "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?

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.

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