Need help in writing aggregate query

Elastic Stack Elasticsearch
1

Hi Team,
I need help in writing below aggregate query.
Use co.elastic.clients.elasticsearch.core.SearchRequest co.elastic.clients.elasticsearch._types.query_dsl.Query. I want to convert below sql query to elastic query.

SELECT
	group AS SummaryGroup,
    COUNT(*) AS NoteCount,
    SUM(AmountParticipation) AS Invested,
    SUM(PrincipalBalance) AS OutstandingPrincipal,
    SUM(PrincipalRepaid) AS PrincipalRepaid,
    SUM(InterestPaid) AS InterestPaid
FROM mytable
WHERE 
     IsSold = 1 AND LenderID = 4477943
GROUP BY group
ORDER BY group desc

JSON document looks like this

{
    "amount_participation: 15000.0,
    "group":"X1",
    "id": "263258-1",
    "interest_paid": 30.0,
    "is_sold": false,
    "lender_id": 4477943,
    "principal_balance": 1000.0,
    "principal_repaid": 200.0
}

Thank you

2

Hi @vkrishna

I think that query below might help you to start:

{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "is_sold": true
          }
        },
        {
          "term": {
            "lender_id": 4477943
          }
        }
      ]
    }
  },
  "aggs": {
    "group_by_summaryGroup": {
      "terms": {
        "field": "group.keyword",
        "order": {
          "_key": "desc"
        }
      },
      "aggs": {
        "note_count": {
          "value_count": {
            "field": "id"
          }
        },
        "invested_sum": {
          "sum": {
            "field": "amount_participation"
          }
        },
        "outstanding_principal_sum": {
          "sum": {
            "field": "principal_balance"
          }
        },
        "principal_repaid_sum": {
          "sum": {
            "field": "principal_repaid"
          }
        },
        "interest_paid_sum": {
          "sum": {
            "field": "interest_paid"
          }
        }
      }
    }
  }
}
3

@RabBit_BR
I am looking for JAVA API query. Could you please help me on that.
Thank you

4

I wrote lot of examples in this repo. This could help you to convert the great answer from @RabBit_BR :wink: