Filter by Aggregation results in Elasticsearch 5

Question

I am using ES 5. With Nest lib on C#.

I have two entities in my model, Contact and events. I have a requirement in which I have to get all contact than have more than N events (very similar to a having query on SQL). Contact are event parents so I can filter using a parent/child strategy.

I was able to get the aggregations for the contacts by I am not able to filter the contact by those aggreagations.

I did something like this:

var queryResult = client.Search<Contact>(s => s
            .Index("contact*")                
            .Query(q => q
                ...
            )
            .Aggregations(a => a
                .Children<Event>("filter_event", ca => ca.Aggregations(ca2 => ca2
                    .Filter("filter1", f => f.Filter(fq => fq.Term(t => t.Field(tf => tf.EventName).Value("Event1")))
                        .Aggregations(fa => fa
                            .Terms("filter1Contacts", v => v.Field(faf => faf.EventContactGuid).Size(int.MaxValue).MinimumDocumentCount(5))
                        )
                    )                        
            )))
        );

With this code I was able to get the aggregations for only those contacts with more than 5 events, but I did not find a way to filter my contact based on those aggregation results.

There is a way to do this in ES 5?

Answer 1

You use a has_child query for this, here's an example to try in Linqpad

void Main()
{
    var pool = new SingleNodeConnectionPool(new Uri("http://localhost:9200"));
    var defaultIndex = "orders";
    var connectionSettings = new ConnectionSettings(pool)
            .DefaultIndex(defaultIndex)
            .InferMappingFor<Order>(m => m
                .IdProperty(f => f.Customer)
            )
            .PrettyJson()
            .DisableDirectStreaming()
            .OnRequestCompleted(response =>
                {
                    // log out the request
                    if (response.RequestBodyInBytes != null)
                    {
                        Console.WriteLine(
                            $"{response.HttpMethod} {response.Uri} \n" +
                            $"{Encoding.UTF8.GetString(response.RequestBodyInBytes)}");
                    }
                    else
                    {
                        Console.WriteLine($"{response.HttpMethod} {response.Uri}");
                    }

                    Console.WriteLine();

                    // log out the response
                    if (response.ResponseBodyInBytes != null)
                    {
                        Console.WriteLine($"Status: {response.HttpStatusCode}\n" +
                                 $"{Encoding.UTF8.GetString(response.ResponseBodyInBytes)}\n" +
                                 $"{new string('-', 30)}\n");
                    }
                    else
                    {
                        Console.WriteLine($"Status: {response.HttpStatusCode}\n" +
                                 $"{new string('-', 30)}\n");
                    }
                });

    var client = new ElasticClient(connectionSettings);

    if (client.IndexExists(defaultIndex).Exists)
        client.DeleteIndex(defaultIndex);

    client.CreateIndex(defaultIndex, c => c
        .Mappings(m => m
            .Map<Order>(mm => mm.AutoMap())
            .Map<OrderLine>(mm => mm
                .Parent<Order>()
                .AutoMap()
            )
        )
    );

    var orders = new[]
    {
        new Order { Customer = "Bilbo Baggins" },
        new Order { Customer = "Gandalf the Grey" }
    };

    var orderlines = new Dictionary<string, OrderLine[]>
    {
        { "Bilbo Baggins",
            new []
            {
                new OrderLine { ItemNumber = 1 },
                new OrderLine { ItemNumber = 2 },
                new OrderLine { ItemNumber = 3 },
                new OrderLine { ItemNumber = 4 },
                new OrderLine { ItemNumber = 5 }
            }
        },
        { "Gandalf the Grey",
            new []
            {
                new OrderLine { ItemNumber = 1 },
                new OrderLine { ItemNumber = 2 },
                new OrderLine { ItemNumber = 3 },
                new OrderLine { ItemNumber = 4 }
            }
        }
    };

    client.IndexMany(orders);

    foreach (var lines in orderlines)
    {
        client.Bulk(b => b
            .IndexMany(lines.Value, (bi, d) => bi.Parent(lines.Key))
        );
    }

    client.Refresh(defaultIndex);

    var queryResult = client.Search<Order>(s => s
        .Query(q => +q
            .HasChild<OrderLine>(c => c
                .Query(cq => +cq.MatchAll())
                // min number of child documents that must match
                .MinChildren(5)
            )
        )
    );
}

public class Order
{
    public string Customer { get; set; }
}

public class OrderLine
{
    public int ItemNumber { get; set; }   
}

the query result returns only Bilbo Baggins

Status: 200
{
  "took" : 5,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 0.0,
    "hits" : [ {
      "_index" : "orders",
      "_type" : "order",
      "_id" : "Bilbo Baggins",
      "_score" : 0.0,
      "_source" : {
        "customer" : "Bilbo Baggins"
      }
    } ]
  }
}