# Aggregate over multiple keys in the document, without knowing the key details **URL:** \ **Category:** Elasticsearch\ **Created:** [November 26, 2018, 7:58pm UTC](https://discuss.elastic.co/t/aggregate-over-multiple-keys-in-the-document-without-knowing-the-key-details/158238 "2018-11-26T19:58:32Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![User\_User](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/user_user/32/43769_2.png) [@User\_User](https://discuss.elastic.co/u/User_User)\ **Post date:** [November 26, 2018, 7:58pm UTC](https://discuss.elastic.co/t/aggregate-over-multiple-keys-in-the-document-without-knowing-the-key-details/158238/1 "2018-11-26T19:58:33Z")
I am new to Elastic Search and was exploring aggregation query. The documents I have are in the format - ```auto {"name":"A", "class":"10th", "subjects":{ "S1":92, "S2":92, "S3":92, } } ``` another document can be - ```auto {"name":"B", "class":"10th", "subjects":{ "S3":92, "S2":92, "S5":92, } } ``` We have about 40k such documents in our ES with the Subjects varying from student to student. The query to the system can be to aggregate all subject-wise scores for a given class. We tried to create a bucket aggregation query as explained in this [guide here](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-filter-aggregation.html), however, this generates a single bucket per document and in our understanding requires an explicit mention of every subject. We want to system to generate subject wise aggregate for the data by executing a single aggregation query, the problem I face is that in our data the subjects could vary from student to student and we don't have a global list of subject keys. We wrote the following script but this only works if we know all possible subjects. GET student\_data\_v1\_1/\_search ```auto { "query" : {"match" : { "class" : "' + query + '" }}, "aggs" : { "my_buckets" : { "terms" : { "field" : "subjects", "size":10000 }, "aggregations": {"the_avg": {"avg": { "field": "subjects.value" }}} }}, "size" : 0 }' ``` but this query only works for the document structure, but does not work multiple subjects are defined where we may not know the key-pair - ```auto {"name":"A", "class":"10th", "subjects":{ "value":93 } } ``` An alternate form the document is present is that the subject is a list of dictionaries - ```auto {"name":"A", "class":"10th", "subjects":[ {"S1":92}, {"S2":92}, {"S3":92}, ] } ``` Having an aggregation query to solve either of the 2 document formats would be helpful. ---
**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\ **Post date:** [November 26, 2018, 9:25pm UTC](https://discuss.elastic.co/t/aggregate-over-multiple-keys-in-the-document-without-knowing-the-key-details/158238/2 "2018-11-26T21:25:33Z")
You will probably have to use [`nested`](https://www.elastic.co/guide/en/elasticsearch/reference/6.5/nested.html) document type and [aggregations](https://www.elastic.co/guide/en/elasticsearch/reference/6.5/search-aggregations-bucket-nested-aggregation.html) for that: ```auto DELETE test PUT test { "mappings": { "_doc": { "properties": { "class": { "type": "keyword" }, "subjects": { "type": "nested", "properties": { "subject": { "type": "keyword" }, "score": { "type": "integer" } } } } } } } PUT test/_doc/1 { "name": "A", "class": "10th", "subjects": [ {"subject": "S1", "score": 80}, {"subject": "S2", "score": 85}, {"subject": "S3", "score": 90} ] } PUT test/_doc/2 { "name": "B", "class": "10th", "subjects": [ {"subject": "S3", "score": 90}, {"subject": "S2", "score": 95}, {"subject": "S5", "score": 100} ] } PUT test/_doc/3 { "name": "C", "class": "9th", "subjects": [ {"subject": "S0", "score": 92}, {"subject": "S10", "score": 92}, {"subject": "S11", "score": 92} ] } GET test/_search { "query": { "match": { "class": "10th" } }, "size": 0, "aggs": { "subjects": { "nested": { "path": "subjects" }, "aggs": { "subject": { "terms": { "field": "subjects.subject" }, "aggs": { "score_stats": { "stats": { "field": "subjects.score" } } } } } } } } ``` ---
**Author:** ![User\_User](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/user_user/32/43769_2.png) [@User\_User](https://discuss.elastic.co/u/User_User)\ **Post date:** [November 27, 2018, 5:42am UTC](https://discuss.elastic.co/t/aggregate-over-multiple-keys-in-the-document-without-knowing-the-key-details/158238/3 "2018-11-27T05:42:46Z")
thanks @Igor_Motov this was exactly what I needed. I am now trying to obtain a weighted average to the individual subject. I have updated the documents to hold a weight with each subject. > PUT test/\_doc/3 > { > "name": "C", > "class": "9th", > "subjects": [ > {"subject": "S0", "score": 92, "weight":20}, > {"subject": "S10", "score": 92, "weight":30}, > {"subject": "S11", "score": 92, "weight":50} > ] > } > GET test/\_search > { > "query": { > "match": { > "class": "10th" > } > }, > "size": 0, > "aggs": { > "subjects": { > "nested": { > "path": "subjects" > }, > "aggs": { > "subject": { > "terms": { > "field": "subjects.subject" > }, > "aggs" : { "weighted\_grade": { "weighted\_avg": { "value": { "field": "subjects.score" }, "weight": { "field": "subjects.weight" } } } } > } > } > } > } > } however I get the following error - > {u'error': {u'col': 312, > u'line': 1, > u'reason': u'Unknown BaseAggregationBuilder [weighted\_avg]', > u'root\_cause': [{u'col': 312, > u'line': 1, > u'reason': u'Unknown BaseAggregationBuilder [weighted\_avg]', > u'type': u'unknown\_named\_object\_exception'}], > u'type': u'unknown\_named\_object\_exception'}, > u'status': 400} ---
**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\ **Post date:** [November 27, 2018, 2:02pm UTC](https://discuss.elastic.co/t/aggregate-over-multiple-keys-in-the-document-without-knowing-the-key-details/158238/4 "2018-11-27T14:02:29Z")
Which version of elasticsearch are you using? ---
**Author:** ![User\_User](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/user_user/32/43769_2.png) [@User\_User](https://discuss.elastic.co/u/User_User)\ **Post date:** [November 27, 2018, 3:21pm UTC](https://discuss.elastic.co/t/aggregate-over-multiple-keys-in-the-document-without-knowing-the-key-details/158238/5 "2018-11-27T15:21:09Z")
"version": { "number": "6.2.2", "build\_hash": "10b1edd", "build\_date": "2018-02-16T19:01:30.685723Z", "build\_snapshot": false, "lucene\_version": "7.2.1", "minimum\_wire\_compatibility\_version": "5.6.0", "minimum\_index\_compatibility\_version": "5.0.0" }, ---
**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\ **Post date:** [November 27, 2018, 3:25pm UTC](https://discuss.elastic.co/t/aggregate-over-multiple-keys-in-the-document-without-knowing-the-key-details/158238/6 "2018-11-27T15:25:08Z")
> "number": "6.2.2" `weighted_avg` was added in 6.4.0. So, your version doesn't support it. ---
**Author:** ![User\_User](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/user_user/32/43769_2.png) [@User\_User](https://discuss.elastic.co/u/User_User)\ **Post date:** [November 27, 2018, 3:37pm UTC](https://discuss.elastic.co/t/aggregate-over-multiple-keys-in-the-document-without-knowing-the-key-details/158238/7 "2018-11-27T15:37:42Z")
great, thanks for helping identify and resolve the issue ---
**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, 3:37pm UTC](https://discuss.elastic.co/t/aggregate-over-multiple-keys-in-the-document-without-knowing-the-key-details/158238/8 "2018-12-25T15:37:49Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.