SUM in couchbase FTS

Can we do aggregation operation SUM in FTS ?
For ex: sum of spending_amount for each customer for a given date range.
If yes, could anyone please share any reference for JAVA SDK.

Thanks in advance.

Hi,

Currently facets don’t have the SUM function, it only supports the COUNT functionality now.
But this can be done at the client side in another hacky way.
While creating the index, you could choose/enable the “store” option for the field (spending_amount) and later during query phase, client could search for the interested date ranges along with retrieving the stored field information as well with the search results.
And then add this up in the client side.

In order to make sure that it works correctly, you need to pass/set the “Size” field of the search request to a value higher than the “Total” hits/result entries for that query. (Else by default, search always returns the TopN results )

reference :https://docs.couchbase.com/server/6.0/fts/fts-response-object-schema.html

regards!

If the requirement is a straight forward filtering and grouping/aggregation, you should use N1QL with GSI. There has been significant improvement in the performance in 5.5 and above.
See: https://blog.couchbase.com/understanding-index-grouping-aggregation-couchbase-n1ql-query/