Difference Between Term and Composite Aggregation

[Shivendra_Singh]

I am new to elastic search. Please correct my understanding related to Term and Composite aggregation-

Using Term aggregation, we can write GROUP BY query of SQL. But the problem arises if there are multiple shards then there may be chance of error (doc_count_error_upper_bound) in the result set as specified in the documentation.

https://www.elastic.co/guide/en/elasticsearch/reference/7.1/search-aggregations-bucket-terms-aggregation.html

Using Composite aggregation, we can overcome this limitation and scan all the records of all the shard in the ES cluster, while writing GROUP BY query. There will be no chance of error in the result set.

Use Case---

As we are developing client reports and we cannot show results with error percentage. Shall we go ahead and use composite aggregation or is there a way to use Term aggregation as well? Our use case is such--

In SQL, we write following type of queries to fetch our results-
Table T, Column A, B, C, D

SELECT A, B, Sum(D) FROM T WHERE A='X' and D='some value' GROUP BY A, B

I know this SQL query can be written either using TERM OR COMPOSITE Aggregation. But my question is, If I want accurate result then shall I use COMPOSITE Aggregation?
Is there a way I can use TERM Aggregation as well without any error. I am asking this as we have two environment one is using version 6.6 whereas the other one is little old and using 5.1 and composite Aggregation is not available in version 5.1

[wangqinghuan (Wang)]

If your cardinality of fields in the term aggregation is not great, setting a size greater than cardinality will enable terms aggregation to return results accurately. In the case of greate cardinality, using composite aggregation to paginate over all composite buckets is a better choice.