# Aggregate a search result? **URL:** \ **Category:** Elasticsearch\ **Created:** [January 21, 2019, 2:12pm UTC](https://discuss.elastic.co/t/aggregate-a-search-result/165061 "2019-01-21T14:12:40Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Wassim\_Ben\_Amor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wassim_ben_amor/32/39799_2.png) [@Wassim\_Ben\_Amor](https://discuss.elastic.co/u/Wassim_Ben_Amor)\ **Post date:** [January 21, 2019, 2:12pm UTC](https://discuss.elastic.co/t/aggregate-a-search-result/165061/1 "2019-01-21T14:12:40Z")
Hello, I have documents with fields: - session\_id - user\_id Every document represent a user request in a conversation. Example : ![1](https://us1.discourse-cdn.com/elastic/original/3X/2/2/2248a3ecf1cefe8a0b7cfece0d5796248b503fbf.png) when I execute a simple aggregation to show for each user how much dialogs he has: > { > "size": 0, > "aggs": { > "2": { > "terms": { > "field": "user\_id.keyword" > }, > "aggs": { > "1": { > "cardinality": { > "field": "session\_id.keyword" > } > } > } > } > } > } which shows : ![56](https://us1.discourse-cdn.com/elastic/original/3X/1/3/13a8c8e168d517270c0657e4d0c7b4fc2055bc60.png) But my goal is to aggregate the dialog count in the result (the value of the keys in the result) of the last aggregation to obtain this : ![50](https://us1.discourse-cdn.com/elastic/original/3X/2/f/2fc084de9d5e449ea93b2105237076708ae5fdd2.png) ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [January 21, 2019, 3:08pm UTC](https://discuss.elastic.co/t/aggregate-a-search-result/165061/2 "2019-01-21T15:08:11Z")
If you have many unique users and you want to do behavioural analysis on them I'd suggest looking at [entity centric indexes](https://twitter.com/elasticmark/status/1009380268409610240). ---
**Author:** ![Wassim\_Ben\_Amor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wassim_ben_amor/32/39799_2.png) [@Wassim\_Ben\_Amor](https://discuss.elastic.co/u/Wassim_Ben_Amor)\ **Post date:** [January 21, 2019, 3:13pm UTC](https://discuss.elastic.co/t/aggregate-a-search-result/165061/3 "2019-01-21T15:13:22Z")
Thank you for your reply, So, there is no solution to make this directly from an ES query ? ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [January 21, 2019, 3:18pm UTC](https://discuss.elastic.co/t/aggregate-a-search-result/165061/4 "2019-01-21T15:18:35Z")
you could probably achieve this with a [scripted aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-scripted-metric-aggregation.html) but not at large scale with many users. ---
**Author:** ![Wassim\_Ben\_Amor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wassim_ben_amor/32/39799_2.png) [@Wassim\_Ben\_Amor](https://discuss.elastic.co/u/Wassim_Ben_Amor)\ **Post date:** [January 21, 2019, 3:22pm UTC](https://discuss.elastic.co/t/aggregate-a-search-result/165061/5 "2019-01-21T15:22:22Z")
Thank you for your reply sir, Since I'm new to scripted aggregations, can you help me by providing a quick implementation for my problem please ? Thank you, ---
**Author:** ![Wassim\_Ben\_Amor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wassim_ben_amor/32/39799_2.png) [@Wassim\_Ben\_Amor](https://discuss.elastic.co/u/Wassim_Ben_Amor)\ **Post date:** [January 21, 2019, 4:37pm UTC](https://discuss.elastic.co/t/aggregate-a-search-result/165061/6 "2019-01-21T16:37:25Z")
The [entity centric indexes](https://twitter.com/elasticmark/status/1009380268409610240) concept is very useful in my case. I have to make an entity per user showing the number of sessions for each user. But for fast solving, I have tried the scripted aggregation, but I found that it loops over all the documents which is not a good idea on a large scale of documents, there is no way to make scripted aggregation on search result ? I don't really want to make aggregation on each bucket, it's a global aggregation but based on the search result. Thank you. ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [January 21, 2019, 4:49pm UTC](https://discuss.elastic.co/t/aggregate-a-search-result/165061/7 "2019-01-21T16:49:10Z")
Aggregations only execute on docs that match search results (unless you're explicitly using a `global` aggregation at the root) ---
**Author:** ![Wassim\_Ben\_Amor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wassim_ben_amor/32/39799_2.png) [@Wassim\_Ben\_Amor](https://discuss.elastic.co/u/Wassim_Ben_Amor)\ **Post date:** [January 21, 2019, 4:57pm UTC](https://discuss.elastic.co/t/aggregate-a-search-result/165061/8 "2019-01-21T16:57:36Z")
Thank you for your reply. I mean in my case, I have all the documents in the search result, I don't filter documents. But what I wanted to do is to group the value obtained in the result : ![47](https://us1.discourse-cdn.com/elastic/original/3X/7/b/7bc81417aa1594d760c156ea7f2c1f7b16d1ce77.png) I have Occurence of 3 =\> 1 and Occurence of 1 =\> 2. So the goal is to group the aggregation result based on values obtained. ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [January 21, 2019, 5:15pm UTC](https://discuss.elastic.co/t/aggregate-a-search-result/165061/9 "2019-01-21T17:15:05Z")
How many unique users will you have in the production system? ---
**Author:** ![Wassim\_Ben\_Amor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wassim_ben_amor/32/39799_2.png) [@Wassim\_Ben\_Amor](https://discuss.elastic.co/u/Wassim_Ben_Amor)\ **Post date:** [January 21, 2019, 5:21pm UTC](https://discuss.elastic.co/t/aggregate-a-search-result/165061/10 "2019-01-21T17:21:38Z")
It's a big number, i'm sure it is more than 1000 daily users, so it's not good to loop all the documents ... ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [January 21, 2019, 5:32pm UTC](https://discuss.elastic.co/t/aggregate-a-search-result/165061/11 "2019-01-21T17:32:23Z")
What you see in your last example JSON results there isn't half of it either. Each cardinality count of the number of unique sessions is derived from either a Map of unique session IDs or a probabilistic data structure based on hashes behind the scenes. It won't be cheap in terms of RAM when you multiply by numbers of users. The entity centric index approach is probably the best way to go for any mass behavioural analysis. ---
**Author:** ![Wassim\_Ben\_Amor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wassim_ben_amor/32/39799_2.png) [@Wassim\_Ben\_Amor](https://discuss.elastic.co/u/Wassim_Ben_Amor)\ **Post date:** [January 22, 2019, 9:34am UTC](https://discuss.elastic.co/t/aggregate-a-search-result/165061/12 "2019-01-22T09:34:54Z")
@Mark_Harwood Thank you for your support. I think the entity centric index approach is the best way to do this also and it will helps to obtain more graphs in the future. But to make this topic useful, I will share the Scripted Metric Aggregation request, so it can help others. > POST test/\_search > { > "size": 0, > "aggs": { > "colorgroups": { > "scripted\_metric": { > "init\_script": "state.transactions = [:]", > "map\_script": "state.transactions[doc['session\_id.keyword'].value] = doc['user\_id.keyword'].value", > "combine\_script": "return state.transactions;", > "reduce\_script": "def result = [:]; def output = [:]; for (a in states) {for (session in a.keySet()) {if (result[a[session]] == null) {result[a[session]]=0} result[a[session]]++;}} for (a in result.values()){if (output[a.toString()]== null) {output[a.toString()]=0} output[a.toString()]++;} return output;" > } > } > } > } ![16](https://us1.discourse-cdn.com/elastic/original/3X/c/e/ced951069781e373f9c27db5f521909470aff07f.png) Thank you. ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [January 22, 2019, 9:52am UTC](https://discuss.elastic.co/t/aggregate-a-search-result/165061/13 "2019-01-22T09:52:47Z")
Glad you got something working but watch out for slow response times and/or "circuit breaker" exceptions for RAM use if you throw this at a lot of data. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 19, 2019, 9:52am UTC](https://discuss.elastic.co/t/aggregate-a-search-result/165061/14 "2019-02-19T09:52:56Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.