NEST C# aggregations - Count and Max

[rd8388 (Richard)]

var sDescriptor = new Nest.SearchDescriptor<MyRecordType>();
sDescriptor.Aggregations(a =>a.Terms("sources", t => t.Field(f => f.sourceName.Suffix("keyword"))));
var searchResponse = _client.Search<MyRecordType>(sDescriptor);

gives me a bucket containing a count of the record grouped by "sourceName".

var sDescriptor = new Nest.SearchDescriptor<MyRecordType>();
sDescriptor.Aggregations(a => a.Max("last_upserted", m => m.Field(p => p.recordUpserted))
var searchResponse = _client.Search<MyRecordType>(sDescriptor);

gives the max value for recordUpserted across the entire index.

How do I combine the two please to get a count of records per source and the value of the most recently upserted record for each source?

MS SQL example of what I would like to achieve...

SELECT SourceName, COUNT(1) AS NumRecords, MAX(UpsertedDate) AS MostRecentUpdate
FROM MyTable
GROUP BY [SourceName]

[rd8388 (Richard)]

Inspired by legacy documentation... https://www.elastic.co/guide/en/elasticsearch/client/net-api/1.x/handling-aggregations.html

The the (slightly smelly) code below is working and will suffice for now.

List<DataSourceInfo> dsi = new List<DataSourceInfo>();
var result = _client.Search<MyRecordType>(s => s
    .Aggregations(a => a.Terms("sources", t =>
                 t.Field(f => f.sourceName.Suffix("keyword"))
    .Aggregations(aa => aa.Max("last_updated", m =>
                 m.Field(f => f.recordUpserted))))));
var sources = result.Aggregations.Terms<string>("sources");
foreach (var item in sources.Buckets) {
    var valueAgg = item.Values.FirstOrDefault() as Nest.ValueAggregate;
    string maxUpsert = DateTime.Parse(valueAgg.ValueAsString).ToString("dd/MM/yyyy HH:mm");                  
    dsi.Add(new DataSourceInfo() {
        SourceName = item.Key,
        NumRecords = (int)item.DocCount,
        MaxUpsert = maxUpsert
    });
}

[forloop (Russ Cam)]

Each bucket in the terms aggregation exposes the helper methods to retrieve a sub aggregation by name, so a slightly cleaner way would be

var searchResponse = client.Search<MyRecordType>(s => s
	.Size(0)
	.Aggregations(a => a
		.Terms("sources", t => t
			.Field(f => f.sourceName.Suffix("keyword"))
			.Aggregations(aa => aa
				.Max("last_updated", m => m
					.Field(f => f.recordUpserted)
				)
			)
		)
	)
);

var sources = searchResponse.Aggregations.Terms<string>("sources");
foreach (var bucket in sources.Buckets)
{
	var valueAgg = bucket.Max("last_updated");
	string maxUpsert = valueAgg.ValueAsString;
}