Aggregate Query - JAVA API query

Elastic Stack Elasticsearch
1

Hi there,
I am looking for equivalent JAVA API query built using co.elastic.clients.elasticsearch.core.SearchRequest.

Here are the documents

{
   "lender_id": 27,
   "orig_date": "2014-02-23",
   "id": "1227-1",
   "status": 3,
   "principal":10
},
{
   "lender_id": 27,
   "orig_date": "2014-02-23",
   "id": "1227-2",
   "status": 3,
   "principal":20
},
{
   "lender_id": 27,
   "orig_date": "2014-02-23",
   "id": "1227-3",
   "status": 4,
   "principal":30
}

SQL Query

SELECT 
   count(*) as count, 
   ROUND( AVG( CAST( DATEDIFF(month, OrigDate, GETDATE()) as FLOAT) ), 0) as NoteAge,
   Principal = ISNULL(SUM(Principal), 0.00) 
FROM mytable
WHERE LenderID = 27 and status = 3;

I am looking for Elastic JAVA API query to achieve the same.

2

Hello, this is a translation of the query that @RabBit_BR provided in the previous thread.

esClient.search(s -> s
	.aggregations("group_by_summaryGroup", a -> a
		.terms(t -> t
			.field("group.keyword")
			.order(NamedValue.of("_key",SortOrder.Desc)
			)
		)
		.aggregations(Map.of("interest_paid_sum", Aggregation.of(ag -> ag
				.sum(su -> su
					.field("interest_paid")
			)),"outstanding_principal_sum", Aggregation.of(agg -> agg
				.sum(su -> su
					.field("principal_balance")
			)),"principal_repaid_sum", Aggregation.of(aggr -> aggr
				.sum(su -> su
					.field("principal_repaid")
			)),"invested_sum", Aggregation.of(aggre -> aggre
				.sum(su -> su
					.field("amount_participation")
			)),"note_count", Aggregation.of(aggreg -> aggreg
				.valueCount(v -> v
					.field("id")
			))))
	)
	.query(q -> q
		.bool(b -> b
			.filter(List.of(Query.of(qu -> qu
					.term(t -> t
						.field("is_sold")
						.value(FieldValue.of(true))
				)),Query.of(que -> que
					.term(t -> t
						.field("lender_id")
						.value(FieldValue.of(4477943))
				))))
		)
	)
	.size(0)
,Void.class);