# Aggregations on nested objects

**URL:** <https://discuss.elastic.co/t/aggregations-on-nested-objects/36986>\
**Category:** Elasticsearch\
**Created:** [December 11, 2015, 1:51pm UTC](https://discuss.elastic.co/t/aggregations-on-nested-objects/36986 "2015-12-11T13:51:17Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mathias\_Schreiber](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mathias_schreiber/32/6584_2.png) [@Mathias\_Schreiber](https://discuss.elastic.co/u/Mathias_Schreiber)\
**Post date:** [December 11, 2015, 1:51pm UTC](https://discuss.elastic.co/t/aggregations-on-nested-objects/36986/1 "2015-12-11T13:51:17Z")

</div>

Hi community,

I am in the very early stage of my application so I'm spending most of my time on mapping and testing out things.  
Scenario:  
Think about something like any App store out there.  
So I thought of having a document like this (I left most of the metadata because it serves no purpose in this post).

```
{
  "app": "foobar",
  "versions": [
    {
      "version": "3.0.1",
      "comments": [
        {
          "username": "dermattes",
          "comment": "A very cool extension",
          "rating": 4
        }
      ]
    }
  ]
}

```

Now what I wanted to achieve is to get the average rating as well as a regular bucket in the sense of "How many 5 star ratings, how many 4 stars etc.".

Is it possible to get this information with my current document schema or would you advise for something different?

Thanks a lot in advance.  
Mattes

---

<div class="post-metadata">

**Author:** ![Eliran\_Moyal](https://avatars.discourse-cdn.com/v4/letter/e/f6c823/32.png) [@Eliran\_Moyal](https://discuss.elastic.co/u/Eliran_Moyal)\
**Post date:** [December 11, 2015, 2:11pm UTC](https://discuss.elastic.co/t/aggregations-on-nested-objects/36986/2 "2015-12-11T14:11:00Z")

</div>

Hey Mattes,  
this is possible!  
you can use the nested aggregation for this purpose.  
term filter on app -\> sub aggregation for nested aggregation with terms aggregation on ratings.

you can also use my plugin and use sql:)

> **[NLPchina/elasticsearch-sql](https://github.com/NLPchina/elasticsearch-sql)**
>
> elasticsearch-sql - Use SQL to query Elasticsearch

  
your query will be something like:

select app,comments.ratings, count(\*)  
from yourIndex  
group by app,nested(comments.ratings)

---

<div class="post-metadata">

**Author:** ![Mathias\_Schreiber](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mathias_schreiber/32/6584_2.png) [@Mathias\_Schreiber](https://discuss.elastic.co/u/Mathias_Schreiber)\
**Post date:** [December 11, 2015, 2:22pm UTC](https://discuss.elastic.co/t/aggregations-on-nested-objects/36986/3 "2015-12-11T14:22:53Z")

</div>

Hi Eliran,

I tried this but I get strange results back.  
My query is in PHP but the message should come across:

```
    $fullRequest = [
        'query' => [
            'match_all' => []
        ],
        'size' => 0,
        'aggregations' => [
            'extension' => [
                'terms' => [
                    'field' => 'extKey'
                ],
                'aggregations' => [
                    'ratings' => [
                        'stats' => [
                            'field' => 'versions.comments.rating'
                        ]
                    ],
                ]
            ]
        ],
    ];

```

The aggregation result is

```
{
    "extension": {
        "buckets": [
            {
                "doc_count": 1,
                "key": "dubuque",
                "ratings": {
                    "avg": 3,
                    "count": 5,
                    "max": 5,
                    "min": 1,
                    "sum": 15
                }
            },
            {
                "doc_count": 1,
                "key": "roberts",
                "ratings": {
                    "avg": 3,
                    "count": 5,
                    "max": 5,
                    "min": 1,
                    "sum": 15
                }
            }
        ],
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0
    }
}

```

Which is odd, because my first entry has 150 comments and my second one has 23 comments.

Any pointers?

---

<div class="post-metadata">

**Author:** ![Eliran\_Moyal](https://avatars.discourse-cdn.com/v4/letter/e/f6c823/32.png) [@Eliran\_Moyal](https://discuss.elastic.co/u/Eliran_Moyal)\
**Post date:** [December 11, 2015, 2:30pm UTC](https://discuss.elastic.co/t/aggregations-on-nested-objects/36986/4 "2015-12-11T14:30:29Z")

</div>

Show your mapping please.  
you may need to put the nested\_type mapping on your field  
the aggregation query should be something like this:

```auto
{
	"size": 0,
	"aggregations": {
		"app": {
			"terms": {
				"field": "app",
				"size": 200
			},
			"aggregations": {
				"versions.comments.rating@NESTED": {
					"nested": {
						"path": "versions.comments"
					},
					"aggregations": {
						"versions.comments.rating": {
							"terms": {
								"field": "versions.comments.rating",
								"size": 0
							}
						}
					}
				}
			}
		}
	}
}

```

---

<div class="post-metadata">

**Author:** ![Mathias\_Schreiber](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mathias_schreiber/32/6584_2.png) [@Mathias\_Schreiber](https://discuss.elastic.co/u/Mathias_Schreiber)\
**Post date:** [December 11, 2015, 2:56pm UTC](https://discuss.elastic.co/t/aggregations-on-nested-objects/36986/5 "2015-12-11T14:56:01Z")

</div>

I tried this (left out some stuff to safe a little time

```
'extension' => [
    'properties' => [
        'extKey' => [
            'type' => 'string',
            'index' => 'not_analyzed'
        ],
        'versions' => [
            'type' => 'nested',
            'properties' => [
                'version' => [
                    'type' => 'string'
                ],
                'comments' => [
                    'type' => 'nested',
                    'properties' => [
                        'ranking' => [
                            'type' => 'integer'
                        ]
                    ]
                ]
            ]
        ]
    ]
]

```

Now ES complains that "nested path [versions.comments] is not nested]"

---

<div class="post-metadata">

**Author:** ![Eliran\_Moyal](https://avatars.discourse-cdn.com/v4/letter/e/f6c823/32.png) [@Eliran\_Moyal](https://discuss.elastic.co/u/Eliran_Moyal)\
**Post date:** [December 11, 2015, 3:04pm UTC](https://discuss.elastic.co/t/aggregations-on-nested-objects/36986/6 "2015-12-11T15:04:52Z")

</div>

extension is the document type or is it a property?  
if it is a property on your document you should put the full path "extension.versions.comments"  
if not i can't see why it didn't work.. lets wait for someone else to answer

---

<div class="post-metadata">

**Author:** ![Mathias\_Schreiber](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mathias_schreiber/32/6584_2.png) [@Mathias\_Schreiber](https://discuss.elastic.co/u/Mathias_Schreiber)\
**Post date:** [December 11, 2015, 3:16pm UTC](https://discuss.elastic.co/t/aggregations-on-nested-objects/36986/7 "2015-12-11T15:16:02Z")

</div>

Excuse me for wasting your time, you were absolutely right, I had an error within my stubbed data.

You, sir, are awesome and thanks a lot for the help

---

<div class="post-metadata">

**Author:** ![Eliran\_Moyal](https://avatars.discourse-cdn.com/v4/letter/e/f6c823/32.png) [@Eliran\_Moyal](https://discuss.elastic.co/u/Eliran_Moyal)\
**Post date:** [December 11, 2015, 3:16pm UTC](https://discuss.elastic.co/t/aggregations-on-nested-objects/36986/8 "2015-12-11T15:16:58Z")

</div>

No problem, you are welcome  
good luck with your project!

---

<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 5, 2017, 11:31pm UTC](https://discuss.elastic.co/t/aggregations-on-nested-objects/36986/9 "2017-07-05T23:31:45Z")

</div>