Aggregations on nested objects


(Mathias Schreiber) #1

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


(Eliran Moyal) #2

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:)


your query will be something like:

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


(Mathias Schreiber) #3

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?


(Eliran Moyal) #4

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

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

(Mathias Schreiber) #5

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]"


(Eliran Moyal) #6

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


(Mathias Schreiber) #7

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


(Eliran Moyal) #8

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


(system) #9