# How can I save the aggregated results to another index? **URL:** \ **Category:** Elasticsearch\ **Created:** [November 12, 2018, 7:30am UTC](https://discuss.elastic.co/t/how-can-i-save-the-aggregated-results-to-another-index/156243 "2018-11-12T07:30:01Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![fookfook](https://avatars.discourse-cdn.com/v4/letter/f/b5a626/32.png) [@fookfook](https://discuss.elastic.co/u/fookfook)\ **Post date:** [November 12, 2018, 7:30am UTC](https://discuss.elastic.co/t/how-can-i-save-the-aggregated-results-to-another-index/156243/1 "2018-11-12T07:30:01Z")
Now, I can save the results of the query to another index,like this: ```auto POST _reindex { "source": { "index": "twitter", "query":{ "term":{"author.keyword":"Alex"} } }, "dest": { "index": "new_twitter" } } ``` The above DSL are like those in SQL ```auto insert into new_twitter select * from twitter where author='Alex' ``` But ,how do I save the aggregated results to another index? Like this SQL statement down here ```auto insert into new_twitter select author,count(1) cnt from twitter group by author ``` ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [November 12, 2018, 8:45am UTC](https://discuss.elastic.co/t/how-can-i-save-the-aggregated-results-to-another-index/156243/2 "2018-11-12T08:45:20Z")
I don't think you can with existing API. You should do that "manually" by yourself. Note that the new Rollup API does similar things but I think it's limited today to time based data. ---
**Author:** ![fookfook](https://avatars.discourse-cdn.com/v4/letter/f/b5a626/32.png) [@fookfook](https://discuss.elastic.co/u/fookfook)\ **Post date:** [November 13, 2018, 12:37am UTC](https://discuss.elastic.co/t/how-can-i-save-the-aggregated-results-to-another-index/156243/4 "2018-11-13T00:37:34Z")
Well, I looked hard at the rollup document and thought it would not solve my problem, because this method is actually compression of time.So, I figured out if I could use scroll to process my data manually, which unfortunately doesn't work, because scroll can only scroll the result from query, but it can't scroll the result from aggregation.Now, I don't know what to do ☹ ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [November 13, 2018, 1:11am UTC](https://discuss.elastic.co/t/how-can-i-save-the-aggregated-results-to-another-index/156243/5 "2018-11-13T01:11:00Z")
Why would you need to scroll the agg? Can't you just take the response and extract from it the agg part and store it as a document? ---
**Author:** ![fookfook](https://avatars.discourse-cdn.com/v4/letter/f/b5a626/32.png) [@fookfook](https://discuss.elastic.co/u/fookfook)\ **Post date:** [November 13, 2018, 1:21am UTC](https://discuss.elastic.co/t/how-can-i-save-the-aggregated-results-to-another-index/156243/6 "2018-11-13T01:21:52Z")
Because the results returned by agg are large, unless I set a very large size for agg, but this can be very memory consuming.If I set a small size and can't get all the agg results, for example, the following is my code so that only 100 or a limited agg can be returned ```auto TermsAggregationBuilder cityAggs=AggregationBuilders.terms("cityAggs").field("cityName.keyword").size(100); SearchResponse scrollResp = esClient.prepareSearch("community") .setScroll(new TimeValue(60000)) .setQuery(query) .addAggregation(cityAggs) .setSize(300) .get(); ``` ```auto AggregationBuilders.terms("cityAggs").field("cityName.keyword").size(100); ``` In this case, setSize=100, which is limited to returning only 100 aggregate results, in fact, I've got a lot of aggregate results, like a million, if I setSize=1,000,000, maybe I don't have enough memory, I guess I'm really sorry, maybe I don't understand ES well enough, and I don't know whether I can clearly express my question ☹ ---
**Author:** ![fookfook](https://avatars.discourse-cdn.com/v4/letter/f/b5a626/32.png) [@fookfook](https://discuss.elastic.co/u/fookfook)\ **Post date:** [November 13, 2018, 6:28am UTC](https://discuss.elastic.co/t/how-can-i-save-the-aggregated-results-to-another-index/156243/7 "2018-11-13T06:28:02Z")
```auto If the request specifies aggregations, only the initial search response will contain the aggregations results. ``` This is what I find in a document, reference [https://www.elastic.co/guide/en/elasticsearch/reference/6.4/search-request-scroll.html](https://www.elastic.co/guide/en/elasticsearch/reference/6.4/search-request-scroll.html) As described in the document, I should be able to scroll only the result of query, not agg ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [November 13, 2018, 11:02am UTC](https://discuss.elastic.co/t/how-can-i-save-the-aggregated-results-to-another-index/156243/8 "2018-11-13T11:02:53Z")
May be this can help: [Terms aggregation | Elasticsearch Guide [8.11] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-size) > If you want to retrieve **all** terms or all combinations of terms in a nested `terms` aggregation you should use the [Composite](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html) aggregation which allows to paginate over all possible terms rather than setting a size greater than the cardinality of the field in the `terms` aggregation. The `terms` aggregation is meant to return the `top` terms and does not allow pagination. But: > I'm really sorry, maybe I don't understand ES well enough, and I don't know whether I can clearly express my question May be you should express why do you want to "export" this to another index? ---
**Author:** ![fookfook](https://avatars.discourse-cdn.com/v4/letter/f/b5a626/32.png) [@fookfook](https://discuss.elastic.co/u/fookfook)\ **Post date:** [November 13, 2018, 2:08pm UTC](https://discuss.elastic.co/t/how-can-i-save-the-aggregated-results-to-another-index/156243/9 "2018-11-13T14:08:45Z")
Thank you very much, Composite Aggregation solves my problem, and I can use the "after" parameter to page the result of the Aggregation, so that I can manually save all the aggregated results to another index. ```auto This aggregation provides a way to stream all buckets of a specific aggregation similarly to what scroll does for documents. ``` I'm doing this because of our needs. We collected buildings data from multiple websites, and the same building may appear on multiple websites. Now, I want to aggregate the buildings, just like SQL ```auto select buildingName from collected_buildings group by buildingName. ``` Then, I need to save these aggregated buildings. Just like SQL ```auto insert into buildings select buildingName from collected_buildings group by buildingName ``` **Then I can use the index "buildings" to do a "non-repetitive" search of a building.** ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [November 13, 2018, 4:11pm UTC](https://discuss.elastic.co/t/how-can-i-save-the-aggregated-results-to-another-index/156243/10 "2018-11-13T16:11:38Z")
I can't comment more but often a way to solve problems in elasticsearch is to solve the problem at index time and not at search time. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 11, 2018, 4:11pm UTC](https://discuss.elastic.co/t/how-can-i-save-the-aggregated-results-to-another-index/156243/11 "2018-12-11T16:11:42Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.