C# Nest & Elasticsearch 6.x : How to filter/count nested fields (in inner List<object>)

Question

Here is my mapping

[ElasticsearchType(Name = "Topic")]
public class Topic
{
    [Number(NumberType.Integer, Coerce = true)]
    public EnumStatus Status { get; set; }

    [Nested]
    public List<KeywordValue> KeywordValues { get; set; }

}

[ElasticsearchType(Name = "KeywordValue")]
public class KeywordValue
{
    [Keyword]
    public string KeywordId { get; set; }

}

I have 10 documents of type Topic in the index, each KeywordValues property/field of the type List<KeywordValue> contains 5 KeywordValue (5 elements in the list).

9 documents have status "Enabled";

I'm trying to count the total number of elements in each nested KeywordValues field. The result returned is 9 but I would like to get 45 (9*5)

I'm doing this:

var response = Topic.CurrentConnection.Search<Topic>(s => s
                .Size(0)
                .Aggregations(fa => fa
                    .Filter("filtered_aggs", f => f
                        .Filter(fd => fd.Term(t => t.Status, Topic.EnumStatus.Enabled))
                        .Aggregations(ta => ta
                                .Nested("kv", n=>n.Path(p => p.KeywordValues)
                                    .Aggregations(aa => aa
                                        .ValueCount("vc", v => v.Field(vf => vf.KeywordValues.First().KeywordId))))
                            )
                        )
                    )
            );


        if (response.IsValid)
        {
            var agg = response.Aggregations.Nested("filtered_aggs");
            var n = agg.Nested("kv");
            var z = n.ValueCount("vc");
            result.Object = z.Value;
        }

Raw query equivalent:

# Request:
{
  "size": 0,
  "aggs": {
    "filtered_aggs": {
      "filter": {
        "term": {
          "Status": {
            "value": 0
          }
        }
      },
      "aggs": {
        "kv": {
          "nested": {
            "path": "KeywordValues"
          },
          "aggs": {
            "vc": {
              "value_count": {
                "field": "KeywordValues.KeywordId"
              }
            }
          }
        }
      }
    }
  }
}
# Response:
{
  "took" : 80,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 10,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "filter#filtered_aggs" : {
      "doc_count" : 9,
      "nested#kv" : {
        "doc_count" : 9,
        "value_count#vc" : {
          "value" : 9
        }
      }
    }
  }
}

Any idea? Thanks a lot.

I already read this post: stackoverflow.com/questions/39485196/… and the question about reverse_nested. I succeed with 45 results but when I'm adding the "filter" it does not work anymore :( — David
– David, Commented May 18, 2018 at 14:10
What is curious if I'm doint this : ` var response = Topic.CurrentConnection.Search<Topic>(s => s .Size(0) .Aggregations(fa => fa .Nested("kv", n => n.Path(p => p.KeywordValues) ) ) ); ` I got the desired result. But if I'm adding the "filter" part it's not. — David
– David, Commented May 18, 2018 at 16:04
What about adding the filter as a bool query filter clause inside a query, instead of a filter agg? — Russ Cam
– Russ Cam, Commented May 18, 2018 at 22:59
Thank you @RussCam I updated with query/bool then aggregation named "kv", you are right it is better but I got the same result. 9 instead of 45. :( — David
– David, Commented May 22, 2018 at 11:41

Russ Cam · Accepted Answer · 2018-05-23 06:56:19Z

0

Here's a working example

private static void Main()
{
    var defaultIndex = "topics";
    var pool = new SingleNodeConnectionPool(new Uri("http://localhost:9200"));

    var settings = new ConnectionSettings(pool)
        .DefaultIndex(defaultIndex);

    var client = new ElasticClient(settings);

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

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

    var documents = Enumerable.Range(1, 10)
        .Select(i => new Topic
        {
            Status = i == 1 ? EnumStatus.Disabled : EnumStatus.Enabled,
            KeywordValues = Enumerable.Range(1, 5)
                .Select(j => new KeywordValue
                {
                    KeywordId = $"keyword {i} {j}"
                }).ToList()
        });

    client.Bulk(b => b
        .IndexMany(documents, (d, document) => d
            .Document(document)
        )
        .Refresh(Refresh.WaitFor)
    );

    client.Search<Topic>(s => s
        .Size(0)
        .Query(q => +q
            .Term(t => t.Status, (int)EnumStatus.Enabled)
        )
        .Aggregations(ta => ta
            .Nested("kv", n => n.Path(p => p.KeywordValues)
                .Aggregations(aa => aa
                    .ValueCount("vc", v => v.Field(vf => vf.KeywordValues.First().KeywordId))))
        )
    );
}

[ElasticsearchType(Name = "Topic")]
public class Topic
{
    [Number(NumberType.Integer, Coerce = true)]
    public EnumStatus Status { get; set; }

    [Nested]
    public List<KeywordValue> KeywordValues { get; set; }

}

[ElasticsearchType(Name = "KeywordValue")]
public class KeywordValue
{
    [Keyword]
    public string KeywordId { get; set; }
}

public enum EnumStatus
{
    Enabled,

    Disabled
}

The response to the search request is

{
  "took" : 9,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 9,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "nested#kv" : {
      "doc_count" : 45,
      "value_count#vc" : {
        "value" : 45
      }
    }
  }
}

answered May 23, 2018 at 6:56

Russ Cam

126k34 gold badges211 silver badges275 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Over a year ago

Thank you very much Russ. It's not working for me (I mean, I still get 9 count instead of 45), maybe I've a mapping issue. I'll investigate and come back to this thread...

Russ Cam Over a year ago

Sounds like it's mapping related @David. You generally can't change existing mappings, so if you already have mappings, it's best to create a new index with a new mapping and try there.

Collectives™ on Stack Overflow

C# Nest & Elasticsearch 6.x : How to filter/count nested fields (in inner List<object>)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related