Group by date with aggr and apply sub aggr with group by date in elastic search

[surendrakandira]

I am trying to apply filter with aggregations.


c# code , I want to apply this logic in elastic search

     queryable = queryable.OrderBy(o => o.Date).ToList();
                var queryable1 = queryable.GroupBy(g => g.Date).Select(x => new GroupChartDto()
                {
                    DailyPnL = x.Sum(s => s.DailyPnL),
                    Date = x.Key,
                    AggregateSum = queryable.Where(w => w.Date.Date <= x.Key.Date).Sum(s => s.DailyPnL)
                }).ToList();

Document structure 

{
                    "_index": "pnls",
                    "_id": "2770",
                    "_score": 0.0,
                    "_source": {
                        "id": 2770,
                        "dailyPnL": 922.0,
                        "date": "2021-07-12T12:00:00Z",
                        "day": "Mon",
                        "aggregateSum": 922.0,
                        "account": "ABC"
                    }
                }

I have applied query 

 {
          "query":{
        "bool": {
        "filter": [
        {
    
    "terms": {
    "account": ["abc","xyz","rs876909kk"]
    }
    },
            {
    "range": {"date": {
    "gte": "2021-07-12",
    "lt": "2022-07-12"}}}
            ]
            }
            },
              "aggs": {
                "group_by_month": {
                  "date_histogram": {
                    "field": "date",
                    "calendar_interval": "month",
                    "format": "yyyy-MM-dd"
                  },
                  "sum_dailyPnL":{
                      "filter": {"range": {"date": {"lt": "group_by_month.key_as_string"}}},
                      "aggs": {"sum_dailyPnL": { "sum": { "field": "dailyPnL" } }}
                  }

 }
      }
    }