# Aggregating object keys to achieve a sum **URL:** \ **Category:** Elasticsearch\ **Created:** [February 3, 2022, 1:40pm UTC](https://discuss.elastic.co/t/aggregating-object-keys-to-achieve-a-sum/296193 "2022-02-03T13:40:32Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![hugomeloo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hugomeloo/32/101228_2.png) [@hugomeloo](https://discuss.elastic.co/u/hugomeloo)\ **Post date:** [February 3, 2022, 1:40pm UTC](https://discuss.elastic.co/t/aggregating-object-keys-to-achieve-a-sum/296193/1 "2022-02-03T13:40:32Z")
I am trying to make an aggregation in which I can sum a number from a specific key of an object. As an example: ```auto curl -XPUT "localhost:9200/products/_mapping" -H 'Content-Type: application/json' -d' { "properties": { "opinions": { "properties": { "topic": { "type": "keyword" }, "count": { "type": "integer" } } } } } ' curl -X POST "localhost:9200/products/doc/_bulk" -H 'Content-Type: application/json' -d' {"index":{"_id":1}} {"opinions": [{"topic": "room", "count":2},{"topic":"kitchen","count":1}]} {"index":{"_id":2}} {"opinions":[{"topic": "room", "count":1},{"topic":"restroom","count":1}]} ' ``` I wanted to have a count of room: 3 kitchen: 1 restroom: 1 I am looking for an aggregation in which I can subaggregate summing the count field. I tried the example below, but I am summing the count field without the topic scope: ```auto curl -X POST "localhost:9200/products/_search?pretty" -H 'Content-Type: application/json' -d' { "size": 0, "aggs": { "opinion": { "terms": { "field": "opinions.topic" }, "aggs": { "total": { "sum": { "field": "opinions.count" } } } } } }' ``` If it makes things easier, I could also have an object like: ```auto { "room": 2, "restroom": 1 } ``` but I have 50 keys like that and I think the mapping is not that flexible. I am using this object as a way to speedup the process, but I am flexible to change this structure. ---
**Author:** ![linkerc](https://avatars.discourse-cdn.com/v4/letter/l/13edae/32.png) [@linkerc](https://discuss.elastic.co/u/linkerc)\ **Post date:** [February 3, 2022, 10:23pm UTC](https://discuss.elastic.co/t/aggregating-object-keys-to-achieve-a-sum/296193/2 "2022-02-03T22:23:53Z")
I think you should reorganize your document structure. It's not intuitive the way you have it. What you might want is to flatten out the document to be something like this: { "topic":"kitchen", "count":1, "xyz": "John" // a field with uniq id to tie multiple documents together } ```auto In your above example first document ("_id":1), you would have 2 documents { "topic":"room","count":2, "xyz":"customer1" }, { "topic":"kitchen","count":1, "xyz":"customer1" } for document ("_id":2), you would also have 2 documents { "topic":"room","count":1, "xyz":"customer2" }, { "topic":"restroom","count":1, "xyz":"customer2" } ``` This way, you can eliminate the array list. By searching for the new field "xyz" you get the array list equivalent. The benefit of flattening your document structure is to make aggregation a lot easier. ---
**Author:** ![hugomeloo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hugomeloo/32/101228_2.png) [@hugomeloo](https://discuss.elastic.co/u/hugomeloo)\ **Post date:** [February 3, 2022, 10:53pm UTC](https://discuss.elastic.co/t/aggregating-object-keys-to-achieve-a-sum/296193/3 "2022-02-03T22:53:57Z")
Hi @linkerc, thank your for your answer. Actually, my document is now something like ```auto { "id":123, "topics": ["room", "kitchen", "room"], ... } ``` But I am not interested in the customer info when I am aggregating. I only want to know the count of the keyword repetitions inside the aggregation. So, if I have 1000 documents in one aggregation, I would count the number of times "room" appeared, also "kitchen" and 50 other keys. I have a possible solution [here](https://discuss.elastic.co/t/counting-each-key-of-an-array-inside-aggregation/296060/5) but it's taking a looong time and I am trying to achieve a faster aggregation. ---
**Author:** ![linkerc](https://avatars.discourse-cdn.com/v4/letter/l/13edae/32.png) [@linkerc](https://discuss.elastic.co/u/linkerc)\ **Post date:** [February 3, 2022, 11:27pm UTC](https://discuss.elastic.co/t/aggregating-object-keys-to-achieve-a-sum/296193/4 "2022-02-03T23:27:47Z")
I think your "possible solution" is better. This kind of aggregation should be very fast. How long is long for you? Another possible speed up is index sorting. It sorts the documents based on the fields so it could skip files during search/aggregation. > **[Index Sorting | Elasticsearch Guide \[master\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/master/index-modules-index-sorting.html)** ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [February 4, 2022, 4:55pm UTC](https://discuss.elastic.co/t/aggregating-object-keys-to-achieve-a-sum/296193/5 "2022-02-04T16:55:14Z")
```auto { "properties": { "opinions": { "properties": { "topic": { "type": "keyword" }, "count": { "type": "integer" } } } } } {"opinions": [{"topic": "room", "count":2},{"topic":"kitchen","count":1}]}a ``` Let me point out one problem of this mapping. You have to use [nested fields](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html) for "opinions" to keep topic and count linked. Arrays of object is flattened internally. And also aggregation query should be changed accordingly. One alternative way is use the first aggregation of the previous topic and use transform to do the aggregation backgroud periodicaly. ---
**Author:** ![hugomeloo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hugomeloo/32/101228_2.png) [@hugomeloo](https://discuss.elastic.co/u/hugomeloo)\ **Post date:** [February 8, 2022, 1:10pm UTC](https://discuss.elastic.co/t/aggregating-object-keys-to-achieve-a-sum/296193/6 "2022-02-08T13:10:32Z")
Yep, I understood the solution would be through the nested field, which was not clear in the documentation.. Thanks! This solution works for me. ```auto curl -XDELETE "localhost:9200/products" curl -XPUT "localhost:9200/products" curl -XPUT "localhost:9200/products/_mapping" -H 'Content-Type: application/json' -d' { "properties": { "opinions": { "type": "nested", "properties": { "topic": {"type": "keyword"}, "count": {"type": "long"} }, "include_in_parent": true } } }' curl -X POST "localhost:9200/products/_bulk" -H 'Content-Type: application/json' -d' {"index":{"_id":1}} {"opinions":[{"topic": "room", "count": 2}, {"topic": "kitchen", "count": 1}]} {"index":{"_id":2}} {"opinions":[{"topic": "room", "count": 1}, {"topic": "restroom", "count": 1}]} ' sleep 2 curl -X POST "localhost:9200/_search?pretty" -H 'Content-Type: application/json' -d' { "size": 0, "aggs": { "opinions": { "nested": {"path": "opinions"}, "aggs": { "per_topic": { "terms": {"field": "opinions.topic"}, "aggs": { "counts": { "sum": {"field": "opinions.count"} } } } } } } } ' ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 8, 2022, 1:10pm UTC](https://discuss.elastic.co/t/aggregating-object-keys-to-achieve-a-sum/296193/7 "2022-03-08T13:10:34Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.