Group by multiple fields using Nest

Question

Given I have following data:

| Date | Value |Count|
| 2021-01-01 | X | 1 |
| 2021-01-01 | X | 2 |
| 2021-01-01 | Y | 1 |
| 2021-02-02 | X | 1 |
| 2021-02-02 | X | 2 |
| 2021-02-02 | Y | 5 |

I want to group by these data using multiple fields. ( Date and Value ).

   Example :  Data.GroupBy(x=> new { x.Date, x.Value });

Expected Result :

| Date | Value | Count |
| 2021-01-01 | X | 3 |
| 2021-01-01 | Y | 1 |
| 2021-02-02 | X | 3 |
| 2021-02-02 | Y | 5 |

How can I execute this query using Nest?

Updated :

Index Mapping :

{
  "samples" : {
    "mappings" : {
      "properties" : {
        "count" : {
          "type" : "long"
        },
        "date" : {
          "type" : "date"
        },
        "value" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        }
      }
    }
  }
}

Answer 1

I have the same issue one month ago, Let's assume your class is like this:

 public class Sample
  {
    public string Date { get; set; }

    public string Value { get; set; }

    public int Count { get; set; }
  }

And the final result is like this:

 public class Report
  {
    public string Date { get; set; }

    public string Value { get; set; }

    public double? SumCount { get; set; }
  }

And search on elastic:

public async Task<List<Report>> GetReportAsync(CancellationToken token)
        {
            var result = await _elasticClient.SearchAsync<Sample>(search => search
                .Aggregations(agg => agg
                    .Terms("result", t => t
                        .Script(sc => sc.Source("doc['date'].value+'#'+doc['value'].value").Lang(ScriptLang.Painless))
                        .Aggregations(a => a
                            .Sum("SumCount", s => s.Field(f => f.Count))
                        )))
                .Source(false)
                .Size(0), token);

            return result.Aggregations.Terms("result").Buckets.Select(x => new Report
            {
                Date = x.Key.Split(new[] { "#" }, StringSplitOptions.RemoveEmptyEntries)[0],
                Value = x.Key.Split(new[] { "#" }, StringSplitOptions.RemoveEmptyEntries)[1],
                SumCount = ((ValueAggregate)x["SumCount"])?.Value
            }).ToList();
        }