# Composite aggregation ordered by time over entire data in database **URL:** \ **Category:** Elasticsearch\ **Created:** [April 8, 2020, 10:59am UTC](https://discuss.elastic.co/t/composite-aggregation-ordered-by-time-over-entire-data-in-database/227106 "2020-04-08T10:59:26Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![oridool](https://avatars.discourse-cdn.com/v4/letter/o/258eb7/32.png) [@oridool](https://discuss.elastic.co/u/oridool)\ **Post date:** [April 8, 2020, 10:59am UTC](https://discuss.elastic.co/t/composite-aggregation-ordered-by-time-over-entire-data-in-database/227106/1 "2020-04-08T10:59:26Z")
Hello, I have a requirement to return aggregations over multiple fields, and present in UI the returned aggregations order by time descending. I wrote the query below which seems to work. The only problem I noticed is that sorting is performed **over the returned buckets only** , rather than over the entire data I have in database. I would like to always get the newest bucket of what I have stored in database, regardless the page size limit I use. Is there an option to do that? Here is my query: ```auto # composite aggregation, multi fields GET events-8e037d0a-b0ef-4316-8ccc-8ec24e96c053-2020.03/_search { "size": 0, "aggs": { "aggregated_events": { "composite": { "size": 100, "sources": [ { "type": { "terms": { "field": "actionInfo.eventType" } } }, { "publisher": { "terms": { "field": "fileInfo.publisher.keyword", "missing_bucket": true } } }, { "hash": { "terms": { "field": "fileInfo.sha1.keyword", "missing_bucket": true } } } ] }, "aggregations": { "max_timestamp": { "max": { "field": "@timestamp" } }, "events_bucket_sort": { "bucket_sort": { "sort": [ { "max_timestamp": { "order": "desc" } } ] } }, "last_event": { "top_hits": { "sort": [ { "@timestamp": { "order": "desc" } } ], "_source": { "includes": [ "fileInfo.fileName", "actionInfo.user" ] }, "size": 1 } } } } } } ``` Thank you, Ori. ---
**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:** [April 8, 2020, 11:11am UTC](https://discuss.elastic.co/t/composite-aggregation-ordered-by-time-over-entire-data-in-database/227106/2 "2020-04-08T11:11:11Z")
There's a stock set of questions that normally get asked to lead you to the right answer. These are available if you run [this wizard](https://plnkr.co/edit/eZr7r3KZW02AxNKAHCQa?p=preview&preview). ---
**Author:** ![oridool](https://avatars.discourse-cdn.com/v4/letter/o/258eb7/32.png) [@oridool](https://discuss.elastic.co/u/oridool)\ **Post date:** [April 12, 2020, 3:22pm UTC](https://discuss.elastic.co/t/composite-aggregation-ordered-by-time-over-entire-data-in-database/227106/3 "2020-04-12T15:22:29Z")
Hi Mark, Thank you for your answer. The wizard does not solve my problem. When trying the wizard, it eventually lead me to "Transforming data" - [https://www.elastic.co/guide/en/elasticsearch/reference/7.6/transforms.html](https://www.elastic.co/guide/en/elasticsearch/reference/7.6/transforms.html) However, this will not work for me because I need to perform the aggregation with a filter query (not all events in database are participating in aggregation), Plus, this is a beta feature not officially released. I still wonder - is there no way to sort the aggregations **before** they are returned by the query? I searched all over, and read all the documentation and forums and still could not find the way to do that. Appreciate any help on that, Ori. ---
**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:** [April 12, 2020, 5:42pm UTC](https://discuss.elastic.co/t/composite-aggregation-ordered-by-time-over-entire-data-in-database/227106/4 "2020-04-12T17:42:10Z")
> [@oridool](#): > > this will not work for me because I need to perform the aggregation with a filter query You can supply a query as part of the transform > **[Create transform API | Elasticsearch Guide \[7.6\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/7.6/put-transform.html#put-transform-request-body)** ---
**Author:** ![oridool](https://avatars.discourse-cdn.com/v4/letter/o/258eb7/32.png) [@oridool](https://discuss.elastic.co/u/oridool)\ **Post date:** [April 12, 2020, 7:16pm UTC](https://discuss.elastic.co/t/composite-aggregation-ordered-by-time-over-entire-data-in-database/227106/5 "2020-04-12T19:16:23Z")
Hi Mark, My filter is dynamic and changing each time I calculate the aggregations because it is coming from UI (a user is selecting some filters and press "search"). 1. Do you suggest to perform a "one-time" transform for every UI request to view the aggregated data (by leaving optional _'frequency'_ and _'sync'_ parameters empty)? 2. Is that the only way in Elasticsearch to sort Composite aggregations (or term aggregations) by the '_max\_timestamp_' as appears in my original query in this thread? 3. If doing such a one-time transform, is the transform API going to be faster than doing the same by my application? Regardless, I fear that iterating over the entire source index and build the aggregated index from it would take more than few seconds. And this will not be a good web user experience. Thank you, Ori. ---
**Author:** ![oridool](https://avatars.discourse-cdn.com/v4/letter/o/258eb7/32.png) [@oridool](https://discuss.elastic.co/u/oridool)\ **Post date:** [April 12, 2020, 10:26pm UTC](https://discuss.elastic.co/t/composite-aggregation-ordered-by-time-over-entire-data-in-database/227106/6 "2020-04-12T22:26:01Z")
Hi again, Mark. So I think I found a solution to my issue, by using term aggregations and _order_ instruction. ```auto GET /events-8e037d0a-b0ef-4316-8ccc-8ec24e96c053-2020.03/_search { "query": { "term": { "fileInfo.targetType.keyword": { "value": "VFPT_EXE" } } }, "size": 0, "aggs": { "types": { "terms": { "field": "fileInfo.sha1.keyword", "size": 13, " **order**": { "latestTimestamp": "desc" } }, "aggs": { "latestTimestamp": { "max": { "field": "@timestamp" } }, "last_event": { "top_hits": { "sort": [ { "@timestamp": { "order": "desc" } } ], "_source": { "includes": [ "@timestamp", "actionInfo.user" ] }, "size": 1 } } } } } } ``` The only problem here is the limitation to a single aggregated field. Adding another field to the aggregation creates a hierarchy of buckets and therefore I cannot sort by timestamp. But - if I need to aggregate by 2 fields f1+f2, I guess I can just create another field named "aggregateBy" during indexing, which is a combination of f1+f2. Then, perform aggregation by the "aggregatedBy" field. Does this sound like a good approach? Thanks, Ori. ---
**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:** [April 13, 2020, 7:39am UTC](https://discuss.elastic.co/t/composite-aggregation-ordered-by-time-over-entire-data-in-database/227106/7 "2020-04-13T07:39:04Z")
Hi, You can use scripts in a terms aggregation to assemble mulitiple fields into a single string at query time. ---
**Author:** ![oridool](https://avatars.discourse-cdn.com/v4/letter/o/258eb7/32.png) [@oridool](https://discuss.elastic.co/u/oridool)\ **Post date:** [April 13, 2020, 10:09am UTC](https://discuss.elastic.co/t/composite-aggregation-ordered-by-time-over-entire-data-in-database/227106/8 "2020-04-13T10:09:53Z")
Thanks Mark, I will look into it. What about performance in that case? Is using script going to affect performance? In this aspect, is it better to prepare the aggregation field while indexing? Ori. ---
**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:** [April 13, 2020, 4:11pm UTC](https://discuss.elastic.co/t/composite-aggregation-ordered-by-time-over-entire-data-in-database/227106/9 "2020-04-13T16:11:40Z")
Scripts will likely be slower. Try benchmark it - it may not be an issue. ---
**Author:** ![oridool](https://avatars.discourse-cdn.com/v4/letter/o/258eb7/32.png) [@oridool](https://discuss.elastic.co/u/oridool)\ **Post date:** [April 13, 2020, 4:24pm UTC](https://discuss.elastic.co/t/composite-aggregation-ordered-by-time-over-entire-data-in-database/227106/10 "2020-04-13T16:24:32Z")
Thank you, I did. Indeed it seems to be slower with a script. I'd stick to create an "aggregateBy" field in my index. Thanks for the assistance, seems I now have a solution. Ori. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [May 11, 2020, 4:24pm UTC](https://discuss.elastic.co/t/composite-aggregation-ordered-by-time-over-entire-data-in-database/227106/11 "2020-05-11T16:24:40Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.